Link to home
Start Free TrialLog in
Avatar of HuiShi
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
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

Avatar of Aneesh
Aneesh
Flag of Canada image

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

Avatar of HuiShi
HuiShi

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
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.
Avatar of HuiShi

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
>>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.
ASKER CERTIFIED SOLUTION
Avatar of Rob Farley
Rob Farley
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial