proc

Question

Post reply

proc

Mike Levan

SSChampion

Points: 14201
More actions
December 4, 2007 at 7:57 am

#181060

How can i use db name as parameter in a store proc?
Like
create proc test
@dbname varchar(30)
as
use @dbname
select empid,jobid from tblemp
GO

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

You need to use dynamic sql if that is what you are trying to do.

declare @SQLCmd nvarchar(4000)

set @SQLCmd = 'use ' + @DBName + ';select * from dbo.myTable'

exec (@SQLCmd)

The above is just a quick description, you need to carefully consider how you are using this.

😎

Sergiy SSC Guru Points: 110208 More actions · Answer 2

You may create procedure as system procedure in master database.

Then it will be executed in context of current database.

_____________
Code for TallyGenerator

Mike Levan SSChampion Points: 14201 More actions · Answer 3

Lynn

What if I have 1000 lines of sql statements instead of just one select statement?

because nvarchar allows jusr 4000 characters

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Haven't had that big if a dynamic sql requirement that I am aware of at least. I have several sql scripts that I use to create tables, views, and stored procs that use dynamic sql; but where I am using those is SQL Server 2005 and I use the varchar(max) to hold the sql command.

I think you may be able to concatenate several variables together in the EXEC call (exec (@SQLCmd1 + @SQLCmd2)).

😎

Mike Levan SSChampion Points: 14201 More actions · Answer 5

If am using sql server 2005, how many characters does nvarchar allow me.

just curious to know if my proc will run in 2005

Mike Levan SSChampion Points: 14201 More actions · Answer 6

Lynn

I am concating my sql statemements and exec as

exec (@sqlcmd1+@sqlcmd2+@sqlcmd3)

I will get 3 result sets but i need only 1 result set, like

exec @sqlcmd1 union exec @sqlcmd2 union exec@sqlcmd3

how is it possible?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Mike, I'd either end SQLCmd1 and SQLCmd2 or start SQLCmd2 and SQLCmd3 with the UNION operator.