How to create procedure with the same WHERE column

Question

How can I create one procedure and the WHERE column is the same?

The WHERE column is QuotationNo

This is the procedure signature:

create procedure (QUO1 int, QUO2 int, QUO3 int, QUO4 int)

Output:

Quote     price     quantity
---------------------------------
1234       2000        1000
5678       2500        4000
9012        3000        4500 
3456        1000        1000

Thanks,

Captain16

Absolutely unclear what you're trying to do / what problem you're trying to solve. Can you show us the **complete** procedure (not just the signature of it) and explain in more detail, **what** you want to do with the `WHERE` clause and the `QuotationNo` column? — marc_s, Nov 05 '12 at 09:33
I'm guessing that what you are after is this: http://stackoverflow.com/questions/337704/parameterizing-an-sql-in-clause - however, if you let us know the `result` you are looking for then we will be able to help more. — dash, Nov 05 '12 at 09:34

score 1 · Accepted Answer · answered Nov 05 '12 at 09:33

CREATE PROCEDURE NameMyProc
    @QUO1 int, @QUO2 int, @QUO3 int, @QUO4 int
AS
    select quote, price, quantity
     from tbl
    where quotationno in (@qu01, @qu02, @qu03, @qu04)
GO

You can also create a table-valued function which would be a lot more flexible, e.g.

CREATE FUNCTION dbo.NameMyFunction (
    @QUO1 int, @QUO2 int, @QUO3 int, @QUO4 int
) RETURNS TABLE AS RETURN
    select quote, price, quantity
     from tbl
    where quotationno in (@qu01, @qu02, @qu03, @qu04)
GO

-- Example usage:
SELECT * FROM dbo.NameMyFunction(1234, 5678, 9012, 3456) T

score 0 · Answer 2 · answered Nov 05 '12 at 09:35

0

If you are giving quo value in runtime

create proc [test]
 (QUO int(11))
as 
begin
select quote,price,quantity from table where quote=@QUO 
end

answered Nov 05 '12 at 09:35

SRIRAM

1,888
2
17
17

How to create procedure with the same WHERE column

2 Answers2