CODEWITHSUNDEEP
sql-serversql-server-2008stored-proceduresparametersdynamic-sql
Ionut
Ionut

Reputation: 839

How to execute a stored procedure with parameters?

I have a database with 40 tables. I want to select data from a certain table by using a single stored procedure. First table (TblSPAU1) has 6 columns, named: ID, COL_SPAU1_EA, COL_SPAU1_EQ, COL_SPAU1_ore, COL_SPAU1_nivel, DateTime. Fourth table (TblSPAU4), for example, has this 6 columns: ID, COL_SPAU4_EA, COL_SPAU4_EQ, COL_SPAU4_ore, COL_SPAU4_nivel, DateTime. So what I want is to select data from table X from DateStart to DateStop. What I've done so far is:

USE [DBRapBreaza]
GO
/****** Object:  StoredProcedure [dbo].[PS_SpauOPompa]    Script Date: 12/19/2018 15:48:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[PS_SpauOPompa]

@DataStart datetime,
@DataStop datetime,
@val int
AS
BEGIN
SET NOCOUNT ON;

declare @sql NVARCHAR(max)
declare @col1 varchar
set @col1='ID'
declare @col2 varchar(25) 
set @col2='COL_SPAU'+CONVERT(VARCHAR, @val)+'_EA' 
declare @col3 varchar 
set @col3='DateTime'

set @sql='select [ID]'+@col2+' FROM [DBRapBreaza].[dbo].[TblSPAU'+CONVERT(VARCHAR, @val)+ '] WHERE DateTime between'+CONVERT(VARCHAR(25), @DataStart,121)+ 'and'+CONVERT(VARCHAR(25), @DataStop,121)+';'

END

EXEC sp_sqlexec @sql, N'@DataStart datetime,@DataStop datetime,@val int',  @DataStart, @DataStop, @val

I want to select, let's say, the ID and fist column from a certain table. @val represents the number of the table I want to select data from. On sp_sqlexec I have a tool-tip which says that I have too many arguments specified. When I execute the EXEC, it throws me this error: Msg 137, Level 15, State 2, Line 1. Must declare the scalar variable "@sql". My question is how should I execute this stored procedure? Many thanks in advance!

Upvotes: 1

Views: 146

Answers (1)

S3S
S3S

Reputation: 25112

First, you need some spaces in your string build. Specifically, change it to:

set @sql= 'select [ID], '+@col2+' FROM [DBRapBreaza].[dbo].[TblSPAU'+CONVERT(VARCHAR, @val)+ '] WHERE DateTime between '''+CONVERT(VARCHAR(25), @DataStart,121)+ ''' and '''+CONVERT(VARCHAR(25), @DataStop,121)+''';'

This will make your SQL look like this:

select [ID], COL_SPAU1_EA FROM [DBRapBreaza].[dbo].[TblSPAU1] WHERE DateTime between '2018-12-20 14:18:02.170' and '2018-12-20 14:18:02.170';

As opposed to the following:

select [ID]COL_SPAU1_EA FROM [DBRapBreaza].[dbo].[TblSPAU1] WHERE DateTime between2018-12-20 14:19:13.167and2018-12-20 14:19:13.167;

Which is missing:

  • A comma after the first column
  • Space between comma names (semantics)
  • Space after the word between
  • Quotes around the dates that will be evaluated from your parameters
  • Space before and after and in the where clause on your between statement

This is why it's always wise to use PRINT @sql while testing your dynamic SQL.

Then, once you move the END to the very bottom of the script, change your execution to:

EXEC sp_executesql  
    @sql, 
    N'@DataStart datetime, @DataStop datetime, @val int',  
    @DataStart, @DataStop, @val

This will leave you with the following code:

USE [DBRapBreaza]
GO
/****** Object:  StoredProcedure [dbo].[PS_SpauOPompa]    Script Date: 12/19/2018 15:48:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[PS_SpauOPompa]

@DataStart datetime,
@DataStop datetime,
@val int
AS
BEGIN
SET NOCOUNT ON;

declare @sql NVARCHAR(max)
declare @col1 varchar   --missing a length here
declare @col2 varchar(25)
declare @col3 varchar   --missing a length here

set @col1='ID'
set @col2='COL_SPAU'+CONVERT(VARCHAR, @val)+'_EA' 
set @col3='DateTime'

set @sql= 'select [ID], '+@col2+' FROM [DBRapBreaza].[dbo].[TblSPAU'+CONVERT(VARCHAR, @val)+ '] WHERE DateTime between '''+CONVERT(VARCHAR(25), @DataStart,121)+ ''' and '''+CONVERT(VARCHAR(25), @DataStop,121)+''';'

--print @sql;


EXEC sp_executesql  
    @sql, 
    N'@DataStart datetime, @DataStop datetime, @val int',  
    @DataStart, @DataStop, @val;

END

Of note, you didn't specify a length for @col1 or @col3 which would default to 8 so be careful there. You never used this variable, so I'm not sure what it's purpose is.

Upvotes: 1

Related Questions