Steve Samson
asked on
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],[Fro
open cur1
fetch next from cur1 into @id, @Company, @Old_Vendor_ID, @New_Vendor_ID
set @Company=ltrim(rtrim(@Comp
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
declare cur1 cursor for
SELECT [id],[Source_Interid],[Fro
open cur1
fetch next from cur1 into @id, @Company, @Old_Vendor_ID, @New_Vendor_ID
set @Company=ltrim(rtrim(@Comp
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
ASKER
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 '.'.
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 '.'.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
lol, 6 minutes :-)
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
@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
ASKER
Work great
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