Solved

Dynamic SQL Question

Posted on 2004-09-21
22
906 Views
Last Modified: 2012-05-05
I'm trying to write a dynamic SQL statement and I can't get the syntax right.  The statement is as follows:

declare @table nvarchar(50)
declare @sql nvarchar(500)
declare @lname nvarchar(50)
declare @return_var integer

set @table = 'table_names'
set @lname = 'smith'
set @return_var = 0


SET @SQL = "IF exists (SELECT 1 FROM "' + @table + '" WHERE fldlname = '" + @lname + "') SET '+@return_var+' = '+@return_var+' + 1"

exec(@SQL)

I know it's just a matter of escaping the variables correctly but I'm pulling my hair out on it.

Thanks in advance,

N
0
Comment
Question by:stretch73
  • 11
  • 5
  • 5
  • +1
22 Comments
 
LVL 17

Accepted Solution

by:
BillAn1 earned 200 total points
ID: 12114197
problem 1 - the quotes should be like this :

SET @SQL = 'IF exists (SELECT 1 FROM ' + @table + ' WHERE fldlname = ''' + @lname + ''') SET ' +@return_var+' = '+@return_var+' + 1'

problem 2 - @return_var is an integer variable, not a dynamic name, so what you actually want is not to have it dynamicly evaluated
You would actually want
SET @SQL = 'IF exists (SELECT 1 FROM ' + @table + ' WHERE fldlname = ''' + @lname + ''') SET @return_var = @return_var + 1'

problem 3 is that you cannot access variables within an exec (@SQL) type of structure - they are local variables, and their scope does not extend to within the called procedure. Instead, you will need to use sp_executeSQL, and OUTPUT type variables.
you can do something like this :


declare @table nvarchar(50)
declare @sql nvarchar(500)
declare @vars nvarchar(500)
declare @lname nvarchar(50)
declare @return_var integer

set @table = 'table_names'
set @lname = 'smith'
set @return_var = 0


SET @SQL = N'IF exists (SELECT 1 FROM ' + @table + ' WHERE fldlname = ''' + @lname + ''') SET @return_var = @return_var + 1'
SET @vars = N'@return_var integer OUTPUT'

exec sp_executeSQL @SQL, @vars, @return_var OUTPUT



0
 
LVL 4

Expert Comment

by:davehilditch
ID: 12114220
Well, what I do is always use single quotes as delimiters, and then if you need to put a single quote inside a string/varchar variable then you have to repeat the single quote.  NB this makes 2 single quotes which looks like double quotes but are very different.

Anyway,

SET @SQL = 'IF exists (SELECT 1 FROM ' + @table + ' WHERE fldlname = ''' + @lname + ''') SET ' + @return_var + ' = ' + @return_var + ' + 1'

Should work.

NB.  No double quotes there, all quotes are single quotes.

Dave Hilditch

0
 

Author Comment

by:stretch73
ID: 12114280
Dave,

That didn't work, there's an error about converting to type varchar
0
 

Author Comment

by:stretch73
ID: 12114292
Bill,

Yeah, me again.  This is the same procedure you've been helping me with.  I'm running this check on six different tables and I just thought it would be cooler to pass in the table name than create six different procedures.  
0
 

Author Comment

by:stretch73
ID: 12114319
Bill

Accidentally submitted last comment before I was done.  I got that to work in Query Analyzer but if I do this for each of the IF statements will it still be returning the value to the procedure:

CREATE PROCEDURE check_employee_dyn

@sql nvarchar(500),
@table varchar(50),
@lname varchar(50),
@fname varchar(50),
@return_var integer output

AS

BEGIN

SET @return_var = 0

*********changed only the first one**********
SET @SQL = N'IF exists (SELECT 1 FROM ' + @table + ' WHERE fldlname = ''' + @lname + ''') SET @return_var = @return_var + 1'
SET @vars = N'@return_var integer OUTPUT'

exec sp_executeSQL @SQL, @vars, @return_var OUTPUT

exec(@sql)
**************************************


IF @return_var = 1

IF exists (select 1 from epls_proc_nonproc_individual WHERE fldfname LIKE left(@fname, 3) + '%' AND fldlname = @lname) SET @return_var = @return_var + 1

IF @return_var  = 2

IF exists (select 1 from epls_proc_nonproc_individual WHERE fldfname LIKE left(@fname, 4) + '%' AND fldlname = @lname) SET @return_var = @return_var + 1

IF @return_var  = 3

IF exists (select 1 from epls_proc_nonproc_individual WHERE fldfname = @fname AND fldlname = @lname ) SET @return_var = @return_var + 1


RETURN @return_var

END
GO
0
 

Author Comment

by:stretch73
ID: 12114385
It gets better, in the following statement:

IF exists (select 1 from epls_proc_nonproc_individual WHERE fldfname LIKE left(@fname, 3) + '%' AND fldlname = @lname) SET @return_var = @return_var + 1

I need to know how to escape the '%' as well.  The three apostrophes didn't seem to work.
0
 
LVL 17

Expert Comment

by:BillAn1
ID: 12114519
Yep, it will return the value OK, as it is an OUTPUT type.

as for escaping the '%'   you just need 2 single quotes each -

@SQL = N'IF exists (select 1 from epls_proc_nonproc_individual WHERE fldfname LIKE left(@fname, 3) + ''%'' AND fldlname = @lname) SET @return_var = @return_var + 1'

the 3 in the other case were because, as well as having a simgle quote in the SQL that needed to be escaped, we also had a single quote which was the end of the string.

0
 

Author Comment

by:stretch73
ID: 12114571
Actually I got smart and just did the conversion before I passed it into the IF statements.  I'm a little slow like that.  Still debugging but so far it looks like it's on the UI side.
0
 

Author Comment

by:stretch73
ID: 12114648
Home stretch, here's what I have:

CREATE PROCEDURE check_employee_dyn

@table varchar(50),
@lname varchar(50),
@fname varchar(50),
@return_var integer output

AS

BEGIN

DECLARE @sql nvarchar(500)
DECLARE @vars nvarchar(500)
DECLARE @fname3 varchar(50)
DECLARE @fname4 varchar(50)

SET @return_var = 0
SET @fname3 = left(@fname,3)
SET @fname4 = left(@fname,4)

SET @SQL = N'IF exists (SELECT 1 FROM ' + @table + ' WHERE fldlname = ''' + @lname + ''') SET @return_var = @return_var + 1'
SET @vars = N'@return_var integer OUTPUT'

EXEC sp_executeSQL @SQL, @vars, @return_var OUTPUT

IF @return_var = 1



SET @SQL = N'IF exists (SELECT 1 FROM ' + @table + ' WHERE fldfname LIKE ''' + @fname3 + ''' AND fldlname = @lname) SET @return_var = @return_var + 1'
SET @vars = N'@return_var integer OUTPUT'

EXEC sp_executeSQL @SQL, @vars, @return_var OUTPUT

IF @return_var  = 2




SET @SQL = N'IF exists (SELECT 1 FROM ' + @table + ' WHERE fldfname LIKE ''' + @fname4 + ''' AND fldlname = @lname) SET @return_var = @return_var + 1'
SET @vars = N'@return_var integer OUTPUT'

EXEC sp_executeSQL @SQL, @vars, @return_var OUTPUT

IF @return_var  = 3




SET @SQL = N'IF exists (SELECT 1 FROM ' + @table + ' WHERE fldlname = ''' + @lname + ''' AND fldlname = @lname ) SET @return_var = @return_var + 1'
SET @vars = N'@return_var integer OUTPUT'


RETURN @return_var

END
GO


It validates as fine in Enterprise Manager but when I run the app I get a "(0x80040E14)[Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the variable '@lname'." error.  
0
 

Author Comment

by:stretch73
ID: 12114702
What is the difference between:  

@table varchar(50),
@lname varchar(50),
@fname varchar(50),
@return_var integer output

AND

DECLARE @sql nvarchar(500)
DECLARE @vars nvarchar(500)
DECLARE @fname3 varchar(50)
DECLARE @fname4 varchar(50)

Do you use the DECLARE statement to describe non-input variables?  I'm a little confused.  I'm passing in table, lname, fname, and return_var.
0
 

Author Comment

by:stretch73
ID: 12114737
Figured out the error but not getting a return value.
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

Author Comment

by:stretch73
ID: 12114746
Here's the latest:

CREATE PROCEDURE check_employee_dyn

@table varchar(50),
@lname varchar(50),
@fname varchar(50),
@return_var integer output

AS

BEGIN

DECLARE @sql nvarchar(500)
DECLARE @vars nvarchar(500)
DECLARE @fname3 varchar(50)
DECLARE @fname4 varchar(50)

SET @return_var = 0
SET @fname3 = left(@fname,3)
SET @fname4 = left(@fname,4)

SET @SQL = N'IF exists (SELECT 1 FROM ' + @table + ' WHERE fldlname = ''' + @lname + ''') SET @return_var = @return_var + 1'
SET @vars = N'@return_var integer OUTPUT'

EXEC sp_executeSQL @SQL, @vars, @return_var OUTPUT

IF @return_var = 1



SET @SQL = N'IF exists (SELECT 1 FROM ' + @table + ' WHERE fldfname LIKE ''' + @fname3 + ''' AND fldlname = ''' + @lname + ''') SET @return_var = @return_var + 1'
SET @vars = N'@return_var integer OUTPUT'

EXEC sp_executeSQL @SQL, @vars, @return_var OUTPUT

IF @return_var  = 2



SET @SQL = N'IF exists (SELECT 1 FROM ' + @table + ' WHERE fldfname LIKE ''' + @fname4 + ''' AND fldlname = ''' + @lname + ''') SET @return_var = @return_var + 1'
SET @vars = N'@return_var integer OUTPUT'

EXEC sp_executeSQL @SQL, @vars, @return_var OUTPUT

IF @return_var  = 3



SET @SQL = N'IF exists (SELECT 1 FROM ' + @table + ' WHERE fldfname = ''' + @fname + ''' AND fldlname = ''' + @lname + ''' ) SET @return_var = @return_var + 1'
SET @vars = N'@return_var integer OUTPUT'


RETURN @return_var

END
GO
0
 

Author Comment

by:stretch73
ID: 12114981
Don't I need to escape the @return_var as well?
0
 

Author Comment

by:stretch73
ID: 12115723
I figured it out, lots of little dumb stuff.  Thanks again Bill, you are the man.

N
0
 
LVL 17

Expert Comment

by:BillAn1
ID: 12120854
See, yoy don't need any help at all - just leave you alone for a few hours and you solve it yourself!!
Just to confrim, what you said is exactly right - input parameters to a procedure do not use the keyword DECLARE, whereas any local variables do.
0
 

Expert Comment

by:softsolvers
ID: 12296415
BillAn1 sir,

i am also facing the same problem .so instead of postinga new question i thought to ask it here only.

can you please tell what is the concept of using N' like  N'@variablename which ihave seen at mant places.


secondly,

mine is a select query followed by an insert query

------------------------------------------------------
Set @sql = 'Select userName from Employee'
EXEC sp_execcutesql @sql,@user output

Set @sql = 'insert into user (userName) values '+ @user
EXEC sp_execcutesql @sql
------------------------------------------------------

now i want to store this @user into another table.how to do that as i am facing some problem related to scope of variable as i don't have any values in @user by the time my next line of code thatis insert query is executed
0
 
LVL 17

Expert Comment

by:BillAn1
ID: 12296606
you really shouldn't do this, as you are supposed to post a new question, and assign points to 'pay' for the answer.
I will give you a brief answer, but if you need followup, you should post a new question.
the N before a string, e.g. N'this is a string' makes it a unicode (2 bytes per character) rather than an ascii (1 byte per character) string.

if you want to assign a variable inside dynamic SQL, then insert into a table using dynamic SQL again, you can do as follows :

declare @user varchar(32)
declare @sql nvarchar(1000)
declare @vars nvarchar(32)
set @vars = '@user varchar(32) output'
Set @sql = N'Select @user = userName from Employee'
EXEC sp_executesql @sql,@vars, @user output
select @user

Set @sql = 'insert into user (userName) values ('''+ @user + ''')'
print @sql
EXEC sp_executesql @sql


however, much simpler is not to use dynamicSQL at all :

declare @user varchar(32)
Select @user = userName from Employee
insert into user (userName) values (@user)

or even simpler again,

insert into user (userName)
select userName from Employee


now all of these solns will ahve problems if there is more than one record in the Employee table
0
 

Expert Comment

by:softsolvers
ID: 12296930
BillAn1 sir,

Thanks for the superior guidance. Sir i had already posted my query (with points) on Experts exchange but none of the database experts are able to answer my query.

But now i have seen a ray of hope in you. Please visit this to see th question.

http://www.experts-exchange.com/Databases/Q_21152064.html

there is a lot of old explaination but you please see the last coment added by us there .

thanking you
eagerly waiting for reply
thanks in advance

0
 

Expert Comment

by:softsolvers
ID: 12297038
I have got some solution but the question still lies there.
As our query is quiet complicated we are unable to use the second method i.e. without using dynamic SQL which BillAn1 told in
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21139349.html. I have included my code below and the problem lies in commented section.

i want the select query in first line of commented section(  *** marked) to return a value @new which i can in turn use in the next insert query


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

declare mycolumns2 cursor for
      select column_name,data_type
      from information_schema.columns
     where
     table_name = @table_name1 and
     column_name <> 'IID' and
     column_name not like 'fk%'
     order by data_type
     declare @column_name1 varchar(100), @data_type1 char(30)
     open mycolumns2
     fetch next from mycolumns2 into @column_name1,@data_type1
     while @@fetch_status = 0
     begin
***************************************************************    
     execute('select'+ '(i.'+@column_name1 + ')'+' '+ 'from myinserted as i inner join mydeleted as d on'+' '+ 'i.'+@column_name1+ '=' +'d.'+ @column_name1+ ' '+
          'where (i.'+ @column_name1 + '<> d.'+@column_name1+' '+ 'and not' + ' (i.'+@column_name1+' '+ 'is null and d.' + @column_name1+ ' '+ 'is null)'+')')

     select @new from myinserted
*******************************************************        
               
          fetch next from mycolumns2 into @column_name1,@data_type1
     end

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


please tell if you need further explaination

thanks in advance
0
 

Expert Comment

by:softsolvers
ID: 12297618
comment added by login anugrah is also an id of

Softsolvers Technologies Pvt Ltd

please consider..........

thanks a lot
0
 

Expert Comment

by:softsolvers
ID: 12297730
if i remove @param from sp_executesql  and also remove "select @result = "  from query string @sql ;
then ateast i can see the name of columns in query analyzer ,

althought use of @result is clear to return value but
please explain what is the use of this parameter ..   i:e @param.    
   
     

                declare @SQL nvarchar(4000)
      declare @param nvarchar(32)
      declare @result nvarchar(100)
      set @param = '@result varchar(100) output'

      set @SQL = N'select '+ 'i.'+@column_name1 + '
      from myinserted as i
      inner join mydeleted as d on i.' + @column_name1 + ' = d.'+ @column_name1 + '
      where (i.'+ @column_name1 + ' <> d.' + @column_name1 + '
      and not (i.'+@column_name1+' '+ 'is null and d.' + @column_name1+ ' is null))'

      exec sp_executeSQL @SQL, @result output
      select @result
-------------------------------------------------------------------------------------

thanks.
0
 
LVL 17

Expert Comment

by:BillAn1
ID: 12298091
pls refer any questions to the open thread at
http://www.experts-exchange.com/Databases/Q_21152064.html
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now