Passing in Variables

• ste.cox

  Mr or Mrs. 500

  Points: 584

  More actions

  August 10, 2007 at 3:09 am

  #197246

  Hi,

  I would like to be able to pass a user set variable into the following statemant

  --------------------------------------------------------------

  DECLARE @sql varchar(1000) DECLARE @stat varchar(10) DECLARE @date varchar(20) DECLARE @sturef varchar(20) DECLARE @tg varchar(10)

  SET @stat = '"Poor"'

  SET @date = '"March2007"'

  SET @tg = '?????'

  SET @sql = 'SELECT a.* FROM OPENQUERY(SERVER4,''SELECT studentID,TG,student FROM Reports

  WHERE (((`classwork` = '

  + @stat + ' or `homework` = ' + @stat + ' or `midterm` = ' + @stat + ' ) AND `session` = ' + @date + ') AND `TG` =' + @tg + ')

  GROUP BY studentID

  HAVING count(*)>=3

  ORDER BY TG ASC'')AS a;'

  EXEC (@sql)

  --------------------------------------------------------------

  This all works fine if i set @tg manually (i.e SET @tg ='"A10-"' but i would like to be able to input the tg at run time.

  any ideas???

  Thanks in advance for any help

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  August 10, 2007 at 4:15 am

  #725936

  Steven

  Just write it as a stored procedure with @tg as your input parameter.

  John

• ste.cox

  Mr or Mrs. 500

  Points: 584

  More actions

  August 10, 2007 at 4:20 am

  #725939

  Sorry about this. Iam still trying to figure out how all of this works (haves done anything like this for over a year)

  did u mean do this?

  ------------------------------

  DECLARE @sql varchar(1000) DECLARE @stat varchar(10) DECLARE @date varchar(20) DECLARE @sturef varchar(20)

  SET @stat = '"Poor"'

  SET @date = '"March2007"'

  SET @sql = 'SELECT a.* FROM OPENQUERY(MSQLREVIEW,''SELECT studentID,TG,student FROM Reports

  WHERE (((`classwork` = '

  + @stat + ' or `homework` = ' + @stat + ' or `midterm` = ' + @stat + ' ) AND `session` = ' + @date + ') AND `TG` ='+@tg+')

  GROUP BY studentID

  HAVING count(*)>=3

  ORDER BY TG ASC'')AS a;'

  EXEC (@sql)

  ------------------------------

  you probably didnt because it doesnt work when i tried it.

  Thanks again

  -------------------------------------

  EDIT: ok it does work like that but i have to put " " round the input which i didnt want to do, also it will be input like this A10-06 but i need to be able to get rid of the 06 (or anything after -) to use it within the database.

  Thanks again again

• jvElecenter

  SSCarpal Tunnel

  Points: 4502

  More actions

  August 10, 2007 at 7:36 am

  #726003

  Steve

   

  You have to use more quotes :

  DECLARE

  @sql varchar(1000) DECLARE @stat varchar(10) DECLARE @date varchar(20) DECLARE @sturef varchar(20)

  SET

  @stat = '"Poor"'

  SET

  @date = '"March2007"'

  SET

  @sql = 'SELECT a.* FROM OPENQUERY(MSQLREVIEW,''SELECT studentID,TG,student FROM Reports

  WHERE (((`classwork` = '''

  +

  @stat + ''' or ' + '''homework''' + ' = ' + @stat + ''' or `midterm` = ''' + @stat + ''' ) AND `session` = ''' + @date + ''') AND `TG` ='''+@tg+''')

  GROUP BY studentID

  HAVING count(*)>=3

  ORDER BY TG ASC'')AS a;'

  EXEC

  (@sql)

  Try something like this.

  
  JV

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  August 10, 2007 at 9:17 am

  #726067

  But now you're using @tg without having declared it.  Look up CREATE PROCEDURE in Books Online.  Your code will look something like this:

  CREATE PROCEDURE dbo.MyProc @tg varchar(10)

  AS

  .......

  GO

  John

• ste.cox

  Mr or Mrs. 500

  Points: 584

  More actions

  August 12, 2007 at 6:05 am

  #726321

  thanks for all the help. seams to be working now

  just need to figure out how to use the id it returns in a where statement to return a name now.

• Clifton Collins

  Default port

  Points: 1433

  More actions

  August 13, 2007 at 7:05 am

  #726413

  You can return the results as a recordset with the name in the row or as an output parameter.

   

  Something like this:

  CREATE PROCEDURE dbo.MyProc

  (

   @tg varchar(10),

   @namereturn varchar(25) OUTPUT

  )

  AS

  ...

  Select @namereturn = somecolumn From yourtable Where id = @tg

   

  Then you can execute the stored procedure, passing the two parameters like this if from sql

   

  exec MyProc 'tg parameter text', @NameReturned OUTPUT

   

  If from vb or C# you would need to configure your command object with parameter objects appropriately.

   

  [font="Arial"]Clifton G. Collins III[/font]

• ste.cox

  Mr or Mrs. 500

  Points: 584

  More actions

  August 13, 2007 at 7:09 am

  #726415

  thanks, will give that ago