if exists ( exec ( 'select....

I'm trying to write a stored proc, using the execute command as the select statement, but sql parser says 'no way.'

the command is this...

if exists ( exec ( 'select * from poll' + @pollnumber + ' where username = ' + @username ) )

the error is "Incorrect syntax near the keyword 'exec'."

is it even posible to use an execute command in place of the sql select statement with the if exists?
bsullivan121499Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Gustavo Perez BuenrostroConnect With a Mentor Commented:
EXISTS only permits a subquery not a statement like EXECUTE. To get more detail see "EXISTS (T-SQL)" topic in BOL.

Post more detail about your problem and I'll give you a better advice.
0
 
bsullivan121499Author Commented:
thanks.

i'm trying to write a stored proc which checks a table for a row with a specific ID.
i've got a LOT of tables in this db that are identical (as far as the columns go).
i figured i'd just make a stored proc which would allow you to specify the table name as one of the input variables.... this way i don't have a stored proc for every single table.

the tables are all named 'poll' + @pollnumber

any idea how i could go through this?
0
 
Gustavo Perez BuenrostroCommented:
-- Check next SP:

create proc sp_CheckValue
 @liPollNumber int
,@liUserName varchar(30)
,@btValueExists bit output
as
begin
  set nocount on
  declare @nvsStatement nvarchar(100)
  set @btValueExists=0

  if object_id('Poll'+cast(@liPollNumber as varchar)) is null return
  set @nvsStatement
     ='select @btValueExists'
     +'=count(username) from Poll'
     +cast(@liPollNumber as varchar)
     +' where username='
     +char(34)+@liUserName+char(34)
  exec sp_executesql
       @nvsStatement
      ,N'@btValueExists bit output'
      ,@btValueExists output
end

-- You can use the SP shown above as follows:

declare @btExists bit
exec sp_CheckValue 99,'stanley',@btExists output

if @btExists=1
  begin
    print 'The users exists'
  end


PD: Let me know if the code works as you need.
0
 
bsullivan121499Author Commented:
not quite what i was looking for... but it's really my fault for not explaining more.  this is what i came up with:

create procedure sp_insert_choice ( @pollnumber varchar (7) , @username varchar (50) , @choice int ) as
exec ( 'if exists( select * from poll' + @pollnumber + ' where username = ' + @username + ')' +'
            select 0
      else
            insert into poll' + @pollnumber + '( username , time , choice ) values( ''' + @username + ''' , getdate() , ' + @choice + ')
            select 1'
      )




0
All Courses

From novice to tech pro — start learning today.