SQL Query with a parameter

Marc Rempel

September 04, 2013 01:16

I am trying to make a sql query activity work with a parameter, but can't get it to work.  The help just has parameter* described, but no examples or further definition.  Does anyone have an example of how to incorporate a parameter into the sql command?

Regards,

Marc

0

• 

  Permanently deleted user

  September 04, 2013 01:55

  Hi Marc,

  I have seen parameters working using a colon syntax. The [:status] in the command should get replaced with the value assigned to the status parameter.

  Command: select * from MyTable where Status = :status

  Parameter: (Name) status, (Value) "Open"

   

  0
• 

  Marc Rempel

  September 04, 2013 03:30

  Thanks Paul,

  It looks like my problem was that I am trying to string together a dynamic query vs just setting a parameter for a specific field. 

  I ended gettting this to work by using some dynamic sql and using the @ instead of the : for the parameter.

  COMMAND:

  declare @sql nvarchar(max);

  set @sql = N'select distinct t.Topic, v.Topic_ID from VariableDesc as v join Topics as t on t.Topic_ID = v.Topic_ID where ' + @topic_filter;

  exec sp_executesql @sql;

  ----

  @topic_filter is set as a parameter that is equal to values like "DataSourceID in (2,3,4) and Geog in (1,2,3,)"

   

  Marc

  0
• 

  Frank Godino

  February 26, 2014 06:09

  This is one of the few examples that I have seen for this SQL Query activity.  Thank you for the information.   I could Not see how the colon notation works.  Here is another

   

  declare @sql nvarchar(max);

  set @sql = N'SELECT MailToName1 AS Owner, MailLine1 AS Address, MailCity AS City, MailState AS State, MailZipCode AS ZipCode, PropID AS [Property ID] FROM V_PARCEL where  PropID IN ' + @PIDS + ' Union SELECT  ''Current resident'' as owner,  Formatted as address , SitusCity as city, ''CA'' as State, ZipCode, PropID as [Property ID] FROM            V_SITUS_ADDRESS_POINT where  TotalExemptionValue != 7000 AND PropID IN ' + @PIDS + ' order by PropID';

  exec sp_executesql @sql;

   

  The @PIDS is added as a String Parameter and initialized with a String Variable.

   

  0

Please sign in to leave a comment.