Stored procedure

Question

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

krayknot SSCertifiable Points: 7998 More actions · Answer 1

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: 323518 More actions · Answer 2

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: 193 More actions · Answer 3

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 · Answer 4

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