Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sp_executesql Expand / Collapse
Author
Message
Posted Monday, April 8, 2013 4:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 27, 2014 9:32 AM
Points: 9, Visits: 95
Hi Guys,

I want to assign value in @string1 variable to @output in sp_executesql how can i do that .Any help will be highly appreciated.The current statement returns null which is not true.

exec sp_executesql @output=@string1

Regards

Viv



Roshan
Post #1440062
Posted Monday, April 8, 2013 4:53 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:07 AM
Points: 40,662, Visits: 37,123
The format for sp_executesql is:
exec sp_executeSQL @SQLString, @ParameterDeclaration, @Output = @Variable
Exact details in Books Online.

Without seeing what you're doing, that's about the best I can offer



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1440067
Posted Monday, April 8, 2013 4:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 27, 2014 9:32 AM
Points: 9, Visits: 95
Here's my query:

This is my first statement:-

set @string1= 'select MAX(backup_set_id)
FROM ['+@server_name+'].msdb.dbo.backupset
WHERE database_name = '''+@db+''' AND type = ''D'''

Second Statement:-

exec sp_executesql @string1, N'@item nvarchar(15) OUTPUT', @output = @item OUTPUT

select @output

I want to capture @output and use it later in a join



Roshan
Post #1440070
Posted Monday, April 8, 2013 5:01 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:07 AM
Points: 40,662, Visits: 37,123
DECLARE @string1 NVARCHAR(1000) 
DECLARE @Output NVARCHAR(15)

SET @String1 = 'select @item = MAX(backup_set_id)
FROM ['+@server_name+'].msdb.dbo.backupset
WHERE database_name = '''+@db+''' AND type = ''D'''

exec sp_executesql @string1, N'@item nvarchar(15) OUTPUT', @item = @output OUTPUT

select @output




Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1440071
Posted Monday, April 8, 2013 5:14 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 27, 2014 9:32 AM
Points: 9, Visits: 95
Thx for the query .But its returning null.@string contains value 652127 but @output shows null


Roshan
Post #1440073
Posted Monday, April 8, 2013 6:54 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:01 AM
Points: 20,877, Visits: 32,920
Post ALL your code, dynamic sql and the sql used to call it.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1440091
Posted Monday, April 8, 2013 7:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 27, 2014 9:32 AM
Points: 9, Visits: 95
i got it.but i have another problem.I have got the server name in a variable @servername.and i want to use it to query other servers msdb (other sql servers are added as linked server).

what would be the syntax

when i run this query i get err:- Could not find server ''+@server_name+'' in sys.servers

select b.* FROM ['+@server_name+'].msdb.dbo.backupset b,
['+@server_name+'].msdb.dbo.backupmediafamily mf,



Roshan
Post #1440106
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse