Use database name from variable in SP

Hi All,

I need to use in stored procedure database which name I am passing as a parameter.
The question is : how can I use that name from parameter to reference database with the same name and use it in cursor declaration. Is that possible at all?

Thank you in advance for any help,
Roman

Roman,

if you create the procedure in the master database when you execute that procedure from another database it will be executed within the context of the database you're in. So you don't need to pass a parameter to the database.



use master
go

create sp_myprocedure
as
select * from sysobjects --will run within the context of the database you're int
go

use northwind
go
exec sp_myprocedure --will return objects in northwind.dbo.sysobjects


Now you haven't explained how you want to use the database name but hopefully this will help you. If you need to pass the database name as a parameter then you'll need to start messing with dynamic sql.



Karl Grambow

www.sqldbcontrol.com

Thank you Karl and Madhivanan for attention to my matter,

I will exlpain my goal in more details.
My application running in such a way that customer can set prefix for database during product installation.
Base name for database is "Clinical" and customer can install it as "Dental_Clinical". I can figured out current database name from ini file and use correct connection string when access SP in "Dental_Clinical"from C# code.
Our product has another database that in current installation has same prefix "Dental_". So assume that the name of second database is "Dental_Lab".
In that stored procedure from "Dental_Clinical" I have cursor that pulling some data from "Dental_Lab" database

DECLARE EncounterProviders_cursor CURSOR FOR
SELECT Dental_Lab.dbo.Results.Description
FROM ClinicalParticipation INNER JOIN
Dental_Lab.dbo.Results.Date ON ClinicalParticipation.Date = Dental_Lab.dbo.Results.Date
WHERE (ClinicalParticipation.ActID = @EncounterID)

I can pass "Dental_Lab" as a parameter to SP to know what current name for "Lab" database is. My question is how can I use that string to access "Dental_Lab" database in my cursor?

Thank you very much in advance

Roman

Read about building dynamic SQL in BOL.

Using dynamic SQL for opening a cursor is problematic, to say the least. You probably need to use dynamic SQL to insert data into a temp table, then run the cursor based on the temp table - that is, if you really need to use a cursor.

Thank you all for attention,
actually I found the way how to use dynamic SQL with cursor
SELECT @sql = 'DECLARE my_cur INSENSITIVE CURSOR FOR ' +
'SELECT col1, col2, col3 FROM ' + @table
EXEC sp_executesql @sql
FETCH NEXT FROM my_cur
and it is working for me.


Madhivanan,

I need cursor to concatenate all names from the records which I got from cursor execution
I am pretty sure there is more proper way to do it just in a query, but not very familiar with TSQL.
If you can point me to more proper solution I would very appreciate your help

OPEN EncounterProviders_Cursor
FETCH NEXT FROM EncounterProviders_Cursor
INTO @ProviderName
WHILE @@FETCH_STATUS = 0
BEGIN
-- Use current ProviderID to get provider name
IF @Providers <> ''
BEGIN
SET @Providers = @Providers + '; '
END
Set @Providers = @Providers + @ProviderName

-- Try to get next record
FETCH NEXT FROM EncounterProviders_Cursor
INTO @ProviderName
END

Roman

Thank you very much everyone. <br />My case is definetely the case when I need to concatenate strings on server side.<br />Actually, I should have been referenced<a target="_blank" href=http://www.sommarskog.se/dynamic_sql.html>http://www.sommarskog.se/dynamic_sql.html</a> as I found solution for using dynamic SQL for cursor in it <img src='/community/emoticons/emotion-1.gif' alt='' /> <br /><br />Thanks,<br />Roman

I wonder why nobody yet mentioned:http://www.sommarskog.se/dynamic_sql.html

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs


Hi Frank,

This link is reallly good.
Thanks Frank.

Ganesan B.
Well defined problem is half way solved.

quote:Originally posted by Madhivanan

quote:Originally posted by balaganesh2k

I wonder why nobody yet mentioned:http://www.sommarskog.se/dynamic_sql.html

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs


Hi Frank,

This link is reallly good.
Thanks Frank.

Ganesan B.
Well defined problem is half way solved.

When you reply, dont copy and paste the reply. Click "Reply with Quote" icon and post your reply

Madhivanan

Failing to plan is Planning to fail

Thanks Madhivanan.


Ganesan B.
Well defined problem is half way solved.