SQL Server Performance Forum – Threads Archive

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

---

Give us more details on what you are trying to do. Why do you want to pass dbname as objects and use Cursor? Madhivanan Failing to plan is Planning to fail

---

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.

---

Why are you using Cursor? Can you post the code? Madhivanan Failing to plan is Planning to fail

---

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

---

Refer this
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true Madhivanan Failing to plan is Planning to fail

---

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

---

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.

---

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.

---

]]>