HuiShi
asked on
How to edit queries with parameters in SQL Server management Studio
Hi there:
I don't know how to run a query with input parameters in SQL Server Management Studio...
For example, when I run the query below, it says Must declare the scalar variable "@gc_warehouse"... Is there anyway to bypass this error and SSMS will pop up a
screen for me to input values?
thanks
Hui
I don't know how to run a query with input parameters in SQL Server Management Studio...
For example, when I run the query below, it says Must declare the scalar variable "@gc_warehouse"... Is there anyway to bypass this error and SSMS will pop up a
screen for me to input values?
thanks
Hui
SELECT dbo.receiveh.receiveh_id AS receiveh_id, dbo.receiveh.creation_date AS creation_date, dbo.receiveh.creation_tm AS creation_tm,
dbo.receiveh.invoice_id AS invoice_id, dbo.receiveh.received_by AS received_by, dbo.receiveh.recipt_date AS recipt_date,
dbo.receiveh.recipt_tm AS recipt_tm, dbo.receiveh.to_warehouse_id AS to_warehouse_id, dbo.receivel.cost AS cost,
dbo.receivel.fr_company_id AS receivel_fr_company_id, dbo.receivel.fr_warehouse_id AS receivel_fr_warehouse_id,
dbo.receivel.orig_doc_line_id AS orig_doc_line_id, dbo.receivel.qty_accepted AS qty_accepted, dbo.receivel.qty_received AS qty_received,
dbo.receivel.receive_dt AS receive_dt, dbo.receivel.to_warehouse_id AS receivel_to_warehouse_id, dbo.receiveh.ref_doc_type_id AS ref_doc_type_id,
dbo.receivel.our_bpart_id AS our_bpart_id, dbo.receiveh.our_refno AS our_refno, dbo.ldmnd_stat.descr AS descr, dbo.bpart.descr AS bpart_descr,
dbo.bpart.search_key AS search_key, dbo.bpart_cost.avg_cost AS avg_cost, dbo.receivel.man_adj_reason_id AS man_adj_reason_id,
dbo.receiveh.z_udf_key_2 AS ps_acct
FROM dbo.receiveh LEFT OUTER JOIN
dbo.receivel ON dbo.receiveh.receiveh_id = dbo.receivel.receiveh_id LEFT OUTER JOIN
dbo.ldmnd_stat ON dbo.receivel.ldmnd_stat_id = dbo.ldmnd_stat.ldmnd_stat_id LEFT OUTER JOIN
dbo.bpart ON dbo.receivel.our_bpart_id = dbo.bpart.bpart_id LEFT OUTER JOIN
dbo.bpart_cost ON dbo.bpart.bpart_id = dbo.bpart_cost.bpart_id
WHERE (dbo.receivel.to_warehouse_id = (CASE WHEN @gc_warehouse = 'ALL' THEN receivel.to_warehouse_id ELSE @gc_warehouse END)) AND
(CAST(FLOOR(CAST(dbo.receiveh.creation_date AS float)) AS datetime) >= CONVERT(datetime, @gc_fromdate)) AND
(CAST(FLOOR(CAST(dbo.receiveh.creation_date AS float)) AS datetime) <= CONVERT(datetime, @gc_todate)
ASKER
hi there:
I did put it in and execute, however, SSMS never pops up a window for me to input the value...any idea?
thanks
I did put it in and execute, however, SSMS never pops up a window for me to input the value...any idea?
thanks
Using the method aneeshattingal suggests, you aren't going to get a popup to input the values, instead, fill them in in this section of the sample he sent:
--you can set the values here
SELECT @gc_warehouse = 'ALL'
,@gc_fromdate = '2001-01-01'
,@gc_todate = GETDATE()
Replace 'ALL', '2001-01-01' and GETDATE() with the appropriate values.
--you can set the values here
SELECT @gc_warehouse = 'ALL'
,@gc_fromdate = '2001-01-01'
,@gc_todate = GETDATE()
Replace 'ALL', '2001-01-01' and GETDATE() with the appropriate values.
ASKER
your suggestion is just hard coding into the query which is not what I am looking for... I want to see a popup window...
thanks
Hui
thanks
Hui
>>I want to see a popup window...<<
Then you need to go back to using MS Access. There is no such animal in MS SQL Server. Nor should there be.
Then you need to go back to using MS Access. There is no such animal in MS SQL Server. Nor should there be.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
--you can set the values here
SELECT @gc_warehouse = 'ALL'
,@gc_fromdate = '2001-01-01'
,@gc_todate = GETDATE()
SELECT dbo.receiveh.receiveh_id AS receiveh_id, dbo.receiveh.creation_date
dbo.receiveh.invoice_id AS invoice_id, dbo.receiveh.received_by AS received_by, dbo.receiveh.recipt_date AS recipt_date,
dbo.receiveh.recipt_tm AS recipt_tm, dbo.receiveh.to_warehouse_
dbo.receivel.fr_company_id
dbo.receivel.orig_doc_line
dbo.receivel.receive_dt AS receive_dt, dbo.receivel.to_warehouse_
dbo.receivel.our_bpart_id AS our_bpart_id, dbo.receiveh.our_refno AS our_refno, dbo.ldmnd_stat.descr AS descr, dbo.bpart.descr AS bpart_descr,
dbo.bpart.search_key AS search_key, dbo.bpart_cost.avg_cost AS avg_cost, dbo.receivel.man_adj_reaso
dbo.receiveh.z_udf_key_2 AS ps_acct
FROM dbo.receiveh LEFT OUTER JOIN
dbo.receivel ON dbo.receiveh.receiveh_id = dbo.receivel.receiveh_id LEFT OUTER JOIN
dbo.ldmnd_stat ON dbo.receivel.ldmnd_stat_id
dbo.bpart ON dbo.receivel.our_bpart_id = dbo.bpart.bpart_id LEFT OUTER JOIN
dbo.bpart_cost ON dbo.bpart.bpart_id = dbo.bpart_cost.bpart_id
WHERE (dbo.receivel.to_warehouse
(CAST(FLOOR(CAST(dbo.recei
(CAST(FLOOR(CAST(dbo.recei
)