CODEWITHSUNDEEP
sqlsql-serverstored-procedures
ken
ken

Reputation: 113

Stored Procedure cant detect SET?

im trying to make a Stored Procedure that reads different .dbf files every time i execute it, making a dynamic Stored Procedure. problem is, im not that good in sql yet and im having errors. this is the error:

Procedure or Function 'readDBF' expects parameter '@sql', which was not supplied.

i know that missing something and/or making something that makes the Stored Procedure not understand but please can someone point in in he right direction?

this is the code:

`

USE devSSIS 
GO 
EXEC master.dbo.sp_MSset_oledb_prop N'VFPOLEDB', N'AllowInProcess', 1 
GO
CREATE PROCEDURE readDBF (
@path nvarchar(1000),
@name nvarchar(50),
@sql varchar(max)
)
AS
BEGIN
set @sql = 'select * from openrowset(''VFPOLEDB.1'', ''' + @path +'''; ''; '', ''select * from ''' + @name + ''')'
END
exec @sql
GO

`

im using sql 2005 btw

Upvotes: 0

Views: 54

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270431

I suspect that you want to define @sql as a local variable in the stored procedure, not a parameter:

CREATE PROCEDURE readDBF (
  @path nvarchar(1000),
  @name nvarchar(50)
) AS
BEGIN
    declare @sql nvarchar(max)
    set @sql = 'select * from openrowset(''VFPOLEDB.1'', ''' + @path +'''; ''; '', ''select * from ''' + @name + ''')';
    exec(@sql);
END;

If you are learning about dynamic SQL, you should be learning to use sp_executesql rather than just exec.

Upvotes: 1

Related Questions