Execute a dynamic variable in a stored procedure 3

[tonys123]

Hi

I am trying to dynamically retrieve my server name and then, from that, run a statement which will vary depending on the server name that is returned. I can obtain the server name dynamically but am unable to pass it to my IF statement; regardless of everything, @srvName in my IF statement appears to be null so the ELSE part of the statement is the one that always runs. How do I get the IF statement to recognise the server name that is returned? An example of my code is:

DECLARE @imp nvarchar(500)
DECLARE @srvName nvarchar(500)
DECLARE @var nvarchar(500)
SET @imp='SELECT @@SERVERNAME'
EXECUTE sp_executesql
@imp,
N'@srvName nvarchar OUTPUT',
@srvName OUTPUT
select @srvName

if @srvName = 'SERVER123' -- whatever @@SERVERNAME returns
set @var= N'select name from user_details where name like ''S%'''
else
set @var= N'select name from user_details where name like ''A%'''

EXECUTE (@var)

Thanks

 

[imex]

Hi,

Try:

DECLARE @srvName nvarchar(500);
SELECT @srvName = @@SERVERNAME;
select @srvName;

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[gmmastros]

I think you are making this a lot harder than it needs to be.

First, the reason you are having a problem is because of the dynamic SQL you are trying to execute it, and the way you are declaring the parameter.

You are calling sp_executesql with a named parameter, but the parameter is not used in the query. Try this instead.

DECLARE @imp nvarchar(500)
DECLARE @srvName nvarchar(500)
DECLARE @var nvarchar(500)
SET @imp='SELECT [!]@srvName = [/!]@@SERVERNAME'
EXECUTE sp_executesql
@imp,
N'@srvName nvarchar OUTPUT',
@srvName OUTPUT
select @srvName

When I run this on my instance of SQL server, I get... S as the output. So then I noticed that you are declaring @srvName in the sp_excutesql line as nvarchar, but without a size. Now try this:

DECLARE @imp nvarchar(500)
DECLARE @srvName nvarchar(500)
DECLARE @var nvarchar(500)
SET @imp='SELECT [!]@srvName = [/!]@@SERVERNAME'
EXECUTE sp_executesql
@imp,
N'@srvName nvarchar[!](500)[/!] OUTPUT',
@srvName OUTPUT
select @srvName

Now you should get you server name in the @srvName variable.

Now... I don't know if you simplified the code a lot for the purposes of posting this question. Lots of people do because they want to change variable names and such. I completely understand when this happens. However, if this is truly the code you want to execute, you can do so without using dynamic SQL, like this:

if @@SERVERNAME = 'SERVER123' -- whatever @@SERVERNAME returns
	select name from user_details where name like 'S%'
else
	select name from user_details where name like 'A%'

or like this:

select name 
from   user_details 
where  (@@SERVERNAME = 'SERVER123' And name like 'S%')
       or
       (name like 'S%')

or even this:

Declare @NameVariable varchar(100)

if @@SERVERNAME = 'SERVER123' -- whatever @@SERVERNAME returns
	Set @NameVariable = 'S'
Else
	Set @NameVariable = 'A'

select name 
from   user_details 
where  name like @NameVariable + '%'

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[tonys123]

Hi Guys

Many thanks to you both - both approaches worked for me

Cheers

Anthony