How to edit queries with parameters in SQL Server management Studio

HuiShi
HuiShi used Ask the Experts™
on
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
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)

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
AneeshDatabase Consultant
Top Expert 2009

Commented:
DECLARE @gc_warehouse varchar(100) , @gc_fromdate datetime , @gc_todate datetime

--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 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)
                      )

Author

Commented:
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
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.
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
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
Top Expert 2012

Commented:
>>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.
Commented:
If you're looking for a reporting mechanism, try making a Reporting Services report using this query (without the DECLARE, etc). Then when you run the report, you will get prompted for the parameter values.

Rob

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial