Transact SQL – Issuing Prepared Statements across databases

Preface

A couple of our previous posts are preamble to this post.

Those posts are:

  1. Transact SQL – Extended Stored Procedures
    Link
  2. SQL Server – Identify Expensive Queries using Michael K. Campbell Script
    Link

Objective

In the “Transact SQL – Extended Stored Procedures” we spoke about Extended Stored Procedures.

We will use one of the Stored Procedures [SP], sys.sp_executesql, in this post.

In the “SQL Server – Identify Expensive Queries using Michael K. Campbell Script” we lamented the fact that the user_name function does not allow us to indicate which database we will like to get the user name from.

BTW, both the object_name and object_schema_name functions allow us to specify the database name.

 

Sample Script

Code


use master
go

set nocount on
go

declare @database		varchar(600) 
declare @sqlFormat		nvarchar(600)
declare @ParmDefinition nvarchar(600)
declare @SQLString		nvarchar(600)

declare @username	 sysname
declare @userid		 int


--set default db
set @database = 'default_demo_db'

--set parm definition
set @ParmDefinition = N'@username sysname output, @userid tinyint'; 

--set SQL String
set @sqlString = 
					  N'USE ' + quotename(@database) + ';'
					+ 'select @username = user_name(@userid) '


--set userid
set @userid = 5

--Execute SQL
EXECUTE [sys].[sp_executesql]
			  @SQLString
			, @ParmDefinition
			, @username = @username output
			, @userid   = @userid

print 'SQLString :- ' + @sqlString
print 'userid	 :- ' + cast(@userid as varchar(30))
print 'username :-' + isNull(@username, '');  





Output

user_name_5

 

References

  1. Microsoft – Developer Network
    • sp_executesql (Transact-SQL)
      • sp_executeSQL
        Link
  2. Microsoft – Support
    • sp_executesql (Transact-SQL)
      • How to specify output parameters when you use the sp_executesql stored procedure in SQL Server
        Link
  3. Bernie Cook
    • Reviewing the T-SQL Generated by LINQ to SQL Within SQL Server Profiler
      Link
  4. Redgate
    • How can we improve SQL Prompt? – Unwrap sp_executesql
      Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s