How do i fix this code to work

I have this Stored Procedure I am writing, and i need to get the Database name from a table to run another query from that stored database name. how can i fix this.

declare cur1 cursor for
SELECT [id],[Source_Interid],[From_Vendor_ID],[To_Vendor_ID] FROM [CE_CUSTOM].[dbo].[dw_Vendor_Merge] Where processed_flag='N'
            
open cur1
fetch next from cur1 into @id, @Company, @Old_Vendor_ID, @New_Vendor_ID
set @Company=ltrim(rtrim(@Company))
while @@FETCH_STATUS = 0
begin


THIS IS THE PROBLEM LINE OF CODE.

              -- Get the Vedors New Name
    Select @New_Vendor_Name = select VENDNAME from @Company..pm00200 where VENDORID = @New_Vendor_ID

End line of code NOTE WHERE IT SAYS @COMPANY NAME THAT IS THE PROBLEM.



 etch next from cur1 into @id, @Company, @Old_Vendor_ID, @New_Vendor_ID
 end
close cur1
deallocate cur1
Steve SamsonAsked:
Who is Participating?
 
rajeevnandanmishraConnect With a Mentor Commented:
Hi,

You need to use the dynamic execution of code. Something like below may be useful.
Please add below code with your code and give it a try.

 
-- Declare a SQL Statement Variable
declare @strSql nvarchar(1000)

-- In the loop of your code assign the value into the @strSql 
select @strSql = 'SELECT @New_Vendor_Name = VENDNAME FROM ' + @Company + '..pm00200 WHERE VENDORID = @New_Vendor_ID'
-- Now execute the below statement. You may need to change the datatype or length
exec sp_executesql @strSql, N'@New_Vendor_Name VARCHAR(50) OUTPUT, @New_Vendor_ID VARCHAR(6)',  @New_Vendor_Name OUTPUT, @New_Vendor_ID 

-- At this point you are having the proper value in the variable @New_Vendor_Name

Open in new window


0
 
Rajkumar GsSoftware EngineerCommented:
Replace
Select @New_Vendor_Name = select VENDNAME from @Company..pm00200 where VENDORID = @New_Vendor_ID
to
Select @New_Vendor_Name = VENDNAME from @Company..pm00200 where VENDORID = @New_Vendor_ID
0
 
Steve SamsonAuthor Commented:
My bad on this, but I had paste that in by mistake. I ment to say exactly what you stated but the problem is this part @Company..pm00200  of  the statement?

 Select @New_Vendor_Name = VENDNAME from @Company..pm00200 where VENDORID = @New_Vendor_ID

I get this error
Msg 102, Level 15, State 1, Procedure ce_sp_DW_Vendor_Merge, Line 40
Incorrect syntax near '.'.
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
JaccoCommented:
Hi,

I think you want something along these lines:

declare @ParameterDefinition nvarchar(500) = '@New_Vendor_Name_OUT nvarchar(100) OUTPUT'
declare @New_Vendor_Name nvarchar(100)

declare @qry nvarchar(4000) = 'Select @New_Vendor_Name_OUT = (select VENDNAME from ' + @Company + '..pm00200 where VENDORID = ' + convert(varchar, @New_Vendor_ID) + ')'

exec sp_executesql  @qry, @ParameterDefinition, @New_Vendor_Name_OUT = @New_Vendor_Name OUTPUT

Regards, Jacco
0
 
JaccoCommented:
lol, 6 minutes :-)
0
 
Rajkumar GsSoftware EngineerCommented:
I agree with rajeevnandanmishra's comment.
@Company is a variable. It cannot be appended directly to actual query string

SELECT * FROM @Company..pm00200
will be executed as it is. Since there is not database with name '@Company' it will be an error
Instead we want the value of that variable
So using dynamic query, it would work
@Company + '..pm00200' will become something like somedbname..pm00200'

Hope it is clear
Raj
0
 
Steve SamsonAuthor Commented:
Work great
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.