How to execute a stored procedure with parameters?

Question

0.00/5 (No votes)

See more:

, +

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.

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!

What I have tried:

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

Posted 20-Dec-18 0:39am

DrgIonuţ

Updated 20-Dec-18 0:58am

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2018-12-20T00:58:00

You have not declared @sql outside the body of the stored procedure. In the SP All you are doing is setting up a variable and doing nothing with it.

Try moving the END to after the EXEC

Edit: Another thought - that looks like a very poor database design.

Firstly you appear to have your DateTime column as type varchar. Use the appropriate column type (i.e. either date or datetime).

Avoid using reserved words for column names but if you do remember to enclose them in square brackets e.g.[DateTime]

Why not have a single table with an additional column that differentiates between the different types then the entire SP becomes

SQL

select [ID], COL_SPAU_EA FROM [DBRapBreaza].[dbo].[TblSPAU]
 WHERE DateTime between @DataStart and @DataStop 
AND SPAU_TYPE = @val;

EDIT:
Well, you claim nothing changes yet I get a completely different set of errors when I moved the END to the correct place. But my apologies for thinking the datetime column was a varchar - I misread the code setting up @sql.

However, I've just realised that you are using sp_sqlexec - change that to sp_executesql - Microsoft recommend the latter over the former.

Then you are going to get syntax errors in your SQL - you need to surround the dates with quotes and include some spaces after "between" and around "and" e.g.

SQL

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)+''';'

In response to your comment

Quote:
As I mentioned, I have 40 tables (TblSPAU1...TblSPAU40) which contains the columns mentioned above. DateTime is declared in DB as datetime. And who's SPAU_TYPE in your above code?

I was trying to give you an alternative database design... instead of 40 tables have just ONE table. SPAU_TYPE in my example would be a value between 1 and 40 … so the rows with SPAU_TYPE = 1 would be the rows you currently have in your TblSPAU1, those rows with SPAU_TYPE = 2 would be the rows you currently have in your TblSPAU2, etc. It's a much better design that is considerably easier to work with and maintain.

As an aside, it is a subjective view, but most experienced programmers avoid prefixing table names with Tbl or column names with Col_. It adds nothing to the documentation, causes extra typing and cause issues further down the line … for example, what if you need to replace what was originally a table with a View? You would have to do a lot of work to change TblSPAU to VwSPAU for no real benefit.

Edit 2 - the full code of the SP that I've been using to test this

SQL

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 [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