Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

set execute problem 2

Status
Not open for further replies.

mimi2

Technical User
Apr 2, 2002
407
CA
Hello,
this is my statement:
set @qry_string = 'select distinct nb1 from table1 where name like "%' + @Name_passed +'%"'
set @nb2 = exec(@qry_string)

i get an error:Incorrect syntax near the keyword 'exec'.

how can i get @nb2??
thanks a lot
 
You don't need to use dynamic SQL here. This will work fine for your query:

Code:
SELECT DISTINCT nb1
FROM table1
WHERE name LIKE '%' + @name_passed + '%'

As far as the variable assignment goes, what are you trying to assign to it? If your query will only return one record and you want that value then use:

Code:
SELECT DISTINCT @nb2 = nb1
FROM ...

--James
 
well, that was simple!
SELECT DISTINCT @nb2 = nb1
FROM table1
WHERE name LIKE '%' + @name_passed + '%'

Thanks a lot.
 
If you needed to use dynamic SQL for your query:

--if the tablename is an input parameter etc
set @TableName = 'table1'

set @qry_string = 'select distinct nb1 from ' + @TableName + ' where name like ''%' + @Name_passed + '%'''

looks like you were using double quotes instead of singles in your example.
 
is it good or bad? Sometimes "" don't work in sql query analyser but work inside the stored procedure.

Still how can i assign a value to the query result:
set @qry_string = 'select distinct nb1 from ' + @TableName + ' where name like ''%' + @Name_passed + '%''' ?

thanks a lot
 
As a rule, you should always use single quotes to delimit character data.

To return the value into a variable using dynamic SQL you need to use the sp_executesql stored proc:

Code:
DECLARE @sql varchar(1000),
  @nb2 varchar(20)

SET @sql = 'SELECT DISTINCT @nb2 = nb1 FROM ' + @table + ' WHERE name LIKE ''%' + @name_passed + '%'''

EXEC sp_executesql @sql, N'@nb2 OUTPUT', @nb2

--James
 
Would it better to do something like this if your query returns multiple fields or would this cause too much overhead?

SET @sql = 'SELECT id, name, address FROM ' + @table + ' WHERE name LIKE ''%' + @name_passed + '%'''

Create table #tmp
(id int,
name varchar(50),
address varchar(50))

insert into #tmp
exec(@sql)

select * from #tmp

drop table #tmp
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top