asked on

Getting error when using sp_executesql

Will someone please tell me why i get this error:
"Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'."

When i execute this simple stored procedure to get the last number (ID) loaded into a column of a table. I'm doing this because the primary key of a lot of tables in my project dont autoincrement

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetLastId]
      -- Add the parameters for the stored procedure here
@table_name nvarchar(30),
@column_name nvarchar(30)
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
SET NOCOUNT ON;
      declare @sp_exec varchar(200)

      set @sp_exec = N'select top 1 @column_name_in from @table_name_in order by @column_name_ in desc'

            -- Insert statements for procedure here
      EXECUTE sp_executesql @sp_exec, N'@table_name_in nvarchar, @column_name_in nvarchar',
                                          @table_name, @column_name

END

chapmandew

make sp_exec an nvarchar(2000) instead

troycomp

ASKER

As soon as i posted this i saw one thing i was doing wrong. @sp_exec should be nvarchar not varchar. Now i get :

Must declare the table variable "@table_name_in".

I tried this but to no avail

EXECUTE sp_executesql @sp_exec, N'@table_name_in nvarchar, @column_name_in nvarchar',
@table_name_in = @table_name, @column_name_in = @column_name

chapmandew

YOu need to provide a param definition...kinda like in this example:

DECLARE @IntVariable int;
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);

/* Build the SQL string one time.*/
SET @SQLString =
N'SELECT EmployeeID, NationalIDNumber, Title, ManagerID
FROM AdventureWorks.HumanResources.Employee
WHERE ManagerID = @ManagerID';
SET @ParmDefinition = N'@ManagerID tinyint';
/* Execute the string with the first parameter value. */
SET @IntVariable = 197;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@ManagerID = @IntVariable;
/* Execute the same string with the second parameter value. */
SET @IntVariable = 109;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@ManagerID = @IntVariable;

troycomp

ASKER

Now i get

Must declare the scalar variable "@column_name_in"

ALTER PROCEDURE [dbo].[up_NWCS_GetLastIdentity]
      -- Add the parameters for the stored procedure here
@table_name nvarchar(30),
@column_name nvarchar(30)
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
SET NOCOUNT ON;
      declare @sp_exec nvarchar(2000);
DECLARE @ParmDefinition1 nvarchar(500);
DECLARE @ParmDefinition2 nvarchar(500);
SET @ParmDefinition1 = N'@table_name_in nvarchar';
SET @ParmDefinition2 = N'@column_name_in nvarchar';

      set @sp_exec = N'select top 1 @column_name_in from @table_name_in order by @column_name_ in desc';

            -- Insert statements for procedure here
      EXECUTE sp_executesql @sp_exec, @ParmDefinition1, @ParmDefinition2,
                                          @table_name_in = @table_name, @column_name_in = @column_name;

END

chapmandew

slight tweak...you can't pass them in that way.

ALTER PROCEDURE [dbo].[up_NWCS_GetLastIdentity]
-- Add the parameters for the stored procedure here
@table_name nvarchar(30),
@column_name nvarchar(30)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @sp_exec nvarchar(2000);
DECLARE @ParmDefinition1 nvarchar(500);
DECLARE @ParmDefinition2 nvarchar(500);
SET @ParmDefinition1 = N'@table_name_in nvarchar';
SET @ParmDefinition2 = N'@column_name_in nvarchar';

set @sp_exec = N'select top 1 ' + @column_name_in + ' from @table_name_in order by ' + @column_name_ in + ' desc';

-- Insert statements for procedure here
EXECUTE sp_executesql @sp_exec, @ParmDefinition1, @ParmDefinition2,
@table_name_in = @table_name;

END

troycomp

ASKER

Same error
Must declare the scalar variable "@column_name_in".

This was the approach i initially tried, but its clear the variable column_name_in doesnt exist. I changed the sp_exec string to column_name and then i got

Must declare the scalar variable "@table_name_in".

chapmandew

whoops...sorry about that..try this:

ALTER PROCEDURE [dbo].[up_NWCS_GetLastIdentity]
-- Add the parameters for the stored procedure here
@table_name nvarchar(30),
@column_name nvarchar(30)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @sp_exec nvarchar(2000);
DECLARE @ParmDefinition1 nvarchar(500);
DECLARE @ParmDefinition2 nvarchar(500);
SET @ParmDefinition1 = N'@table_name_in nvarchar';
SET @ParmDefinition2 = N'@column_name_in nvarchar';

set @sp_exec = N'select top 1 ' + @column_name + ' from @table_name order by ' + @column_name + ' desc';

-- Insert statements for procedure here
EXECUTE sp_executesql @sp_exec, @ParmDefinition1, @ParmDefinition2,
@table_name_in = @table_name;

END

troycomp

ASKER

Getting:
Must declare the table variable "@table_name".

I get these errors when i'm actually running the sproc in sql 2005 and not when i go to modify procedure and execute in order to compile the sproc. If you test this against northwind or adventureworks, you'll get the same error.

chapmandew

crap. swap this out.

set @sp_exec = N'select top 1 ' + @column_name + ' from ' + @table_name + ' order by ' + @column_name + ' desc';

troycomp

ASKER

removed the parameter definitions and just ran:
EXECUTE sp_executesql @sp_exec;

and it worked....

Now do you think this is prone to sql injection? Also do you like my idea of how to get the last ID of a column? Meaning this sproc will be called ALOT and will it bog down sql server?

ASKER CERTIFIED SOLUTION

chapmandew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

troycomp

ASKER

Thanks for all your help Chad.