Stored procedure

• Nandy

  Ten Centuries

  Points: 1016

  More actions

  November 14, 2008 at 5:13 am

  #196353

  Hi All,

  I have written stored procedure, based on selections I need to check values from the table. Here I will get tablename from the query and I need to pass the tablename in another query to fetch the values. When ever I pass the tablename in the if loop its giving error like incorrect syntax error near select. Please suggest

  declare @Tablename varchar(30)

  declare @fieldname varchar(20)

  declare @fieldvalue varchar(10)

  set @Tablename='t_trans_auth'

  set @Fieldname='Payeeid'

  set @fieldvalue=1

  begin

  if not exists('select'+@Fieldname +' from '+@TableName+ ' where '+ @Fieldname+'='+ @Fieldvalue)

  if (@@rowcount=0)

  Begin

  Raiserror (65537,11,1)

  return -1

  End

  end

  Please advise.

  Cheers,

  Nandy

• krayknot

  SSCertifiable

  Points: 7998

  More actions

  November 14, 2008 at 5:18 am

  #898490

  Nandy (11/14/2008)

  ---

  Hi All,

  I have written stored procedure, based on selections I need to check values from the table. Here I will get tablename from the query and I need to pass the tablename in another query to fetch the values. When ever I pass the tablename in the if loop its giving error like incorrect syntax error near select. Please suggest

  declare @Tablename varchar(30)

  declare @fieldname varchar(20)

  declare @fieldvalue varchar(10)

  set @Tablename='t_trans_auth'

  set @Fieldname='Payeeid'

  set @fieldvalue=1

  begin

  if not exists('select'+@Fieldname +' from '+@TableName+ ' where '+ @Fieldname+'='+ @Fieldvalue)

  if (@@rowcount=0)

  Begin

  Raiserror (65537,11,1)

  return -1

  End

  end

  Please advise.

  Cheers,

  Nandy

  IF NOT EXISTS (SELECT * FROM tablename where employeeid = @EmployeeId)

  BEGIN

  UPDATE

  SET LimitAccessByClubs = 0

  WHERE = @EmployeeId

  END

  kshitij kumar
  kshitij@krayknot.com
  www.krayknot.com

• Lowell

  SSC Guru

  Points: 323482

  More actions

  November 14, 2008 at 6:18 am

  #898516

  you can't use a variable for an objectname..so you cannot stick something in to replace a tablename or fieldname directly in a statement...you have to use dynamic sql:

  fails:

  if not exists('select'+@Fieldname +' from '+@TableName+ ' where '+ @Fieldname+'='+ @Fieldvalue)

  works:

  exec('select ' + @Fieldname + ' from ' + @TableName+ ' where ' + @Fieldname + ' = ' + @Fieldvalue)

  if @@rowcount.......

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• aftabsatti

  SSC Enthusiast

  Points: 173

  More actions

  November 14, 2008 at 12:47 pm

  #898830

  You can use the dynamic SQL for this, and make sure you have turned on the set count on. and use @@RowCount to check the record count of last executed query.

• Nandy

  Ten Centuries

  Points: 1016

  More actions

  November 16, 2008 at 9:35 pm

  #899154

  Hi All,

  I have used the dynamic SQL and executed, and I used rowcount to check whether the SQL returning the value or not.

  Many thanks for your help.

  Nandy