set execute problem 2

[mimi2]

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

 

[JamesLean]

You don't need to use dynamic SQL here. This will work fine for your query:

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:

SELECT DISTINCT @nb2 = nb1
FROM ...

--James

 

[mimi2]

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

Thanks a lot.

 

[HyperRon]

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.

 

[mimi2]

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

 

[JamesLean]

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:

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

 

[HyperRon]

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

 

[mimi2]

Thanks guys