Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Pete Sass
  Where is Pete Sass?
 Marathon, Ontario
 Canada
 Pete Sass
 To: Nilson Rishi
  Where is Nilson Rishi?
 Bangkok
 Thailand
 Nilson Rishi
 Tags
Subject: RE: SQL Server database name as variable
Thread ID: 331130 Message ID: 331480 # Views: 30 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Friday, December 30, 2011 10:11:40 AM         
   


> Hi,
>
> I got an error for the code:
> Msg 102, Level 15, State 1, Line 10
> Incorrect syntax near ')'.
>
> Anything wrong?
>
> Regards, and happy new year
> Nilson


Hi,
Yep, something wrong and is not working... I will look at my code to see exactly what I did to pass a database name as a stored procedure parameter.

This is what I did.

CREATE PROCEDURE sp_Griffin
@DBName sysname
AS
DECLARE @cmd nvarchar(2000)
SET @cmd = N'SELECT * FROM ' + @DBName + '.dbo.Griffin'
exec (@cmd)
GO

You have to use dynamic SQL. Security risks exist on using dynamic SQL as the user who runs the procedure much have access to the table or tables listed in the dynamic SQL. Dynamic SQL is subject to a SQL injection attack.

Pete "the IceMan", from the Great White North of Canada.
www.marathongriffincomputers.com

ENTIRE THREAD

SQL Server database name as variable Posted by Nilson Rishi @ 12/27/2011 7:36:00 AM
RE: SQL Server database name as variable Posted by Pete Sass @ 12/27/2011 2:50:38 PM
RE: SQL Server database name as variable Posted by Cetin Basoz @ 12/27/2011 4:41:52 PM
RE: SQL Server database name as variable Posted by Pete Sass @ 12/27/2011 5:51:56 PM
RE: SQL Server database name as variable Posted by Cetin Basoz @ 12/30/2011 1:52:49 PM
RE: SQL Server database name as variable Posted by Pete Sass @ 12/30/2011 6:11:09 PM
RE: SQL Server database name as variable Posted by Pete Sass @ 12/30/2011 6:50:34 PM
RE: SQL Server database name as variable Posted by Nilson Rishi @ 12/30/2011 9:24:36 AM
RE: SQL Server database name as variable Posted by Pete Sass @ 12/30/2011 10:11:40 AM
RE: SQL Server database name as variable Posted by Nilson Rishi @ 12/31/2011 7:05:27 AM