Getting store procedure records with parameter

Question

0.00/5 (No votes)

See more:

SQL-Server-2014

Hi,
I am trying to records from my Sales table with two parameters based on the month. Instead of the getting the exact months chosen, it fires me all the records. How can I go about it?

When I include the column Monat in a WHERE Clause, then I get an empty results

My sp:

SQL

CREATE PROC [dbo].[spMonthInfo]
(   
@Periode1 NVARCHAR (255),
@Periode2 NVARCHAR (255) 
)
AS
BEGIN
DECLARE @Dynamictbl nvarchar(MAX) =
N'SELECT
[t].[Monat], [t].[Project],[t].[Description],
[t].[Finance], --[t].[Project] AS [Entrance],

SUM(CASE WHEN [t].Monat = ' + @Periode1 + ' THEN [t].[Amount1] END) AS [Amount1Feb],
SUM(CASE WHEN [t].Monat = ' + @Periode2 + ' THEN [t].[Amount1] END) AS [Amount1March],
SUM(CASE WHEN [t].Monat = ' + @Periode1 + ' THEN [t].[Amount2] END) AS [Amount2Feb],
SUM(CASE WHEN [t].Monat = ' + @Periode2 + ' THEN [t].[ Amount2] END) AS [Amount2March],

FROM [dbo].[tblSales]


GROUP BY [t].[Monat],[t].[Project],[t].[Description],
[t].[Finance], --[t].[Project] AS [Entrance],

EXECUTE sp_executesql @Dynamictbl

END
GO
-- 
EXEC spMonthInfo @Periode1 = '02.2011', @Periode2 = '03.2011'

The Problem: [^]

Posted 20-May-15 3:10am

mikybrain1

Add a Solution

Comments

CHill60 20-May-15 9:27am

Are you absolutely sure that the format of the input parameters @Periode1 and @Periode2 match the data in the column Monat?
I also think you might need an ELSE 0 for those case statements

mikybrain1 20-May-15 9:49am

Yeah. They do match match exactly. I have added a where clause to it and it seems to be functioning WHERE [t].[Monat] = ' + @Periode1 +' OR [t].[Monat] = '+ @Periode2 + ' AND [t].[Region] = ''AB'' but then the next problem. This line isn't applying. AND [t].[Region] = ''AB''. Do u please have a clue?

_Asif_ 20-May-15 9:33am

Monat type is DateTime?

mikybrain1 20-May-15 9:50am

No it's a NVARCHAR

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)

Richard Deeming · Accepted Answer · 2015-05-20T03:49:00

A perfect example of why stored procedures don't make you immune to SQL Injection[^] vulnerabilities.

NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Within SQL, use sp_executesql[^] to execute a dynamic query with parameters.

You also have extra trailing commas within your dynamic query which need to be removed.

SQL

CREATE PROC [dbo].[spMonthInfo]
(   
    @Periode1 NVARCHAR(255),
    @Periode2 NVARCHAR(255) 
)
AS
BEGIN
    DECLARE @Dynamictbl nvarchar(MAX) = N'SELECT
        [t].[Monat], [t].[Project],[t].[Description],
        [t].[Finance], --[t].[Project] AS [Entrance],
        SUM(CASE WHEN [t].Monat = @Periode1 THEN [t].[Amount1] END) AS [Amount1Feb],
        SUM(CASE WHEN [t].Monat = @Periode2 THEN [t].[Amount1] END) AS [Amount1March],
        SUM(CASE WHEN [t].Monat = @Periode1 THEN [t].[Amount2] END) AS [Amount2Feb],
        SUM(CASE WHEN [t].Monat = @Periode2 THEN [t].[Amount2] END) AS [Amount2March]
    FROM 
        [dbo].[tblSales]
    GROUP BY 
        [t].[Monat],
        [t].[Project],
        [t].[Description],
        [t].[Finance]
    ;';
 
    EXECUTE sp_executesql @Dynamictbl, 
        N'@Periode1 NVARCHAR(255), @Periode2 NVARCHAR(255)',
        @Periode1 = @Periode1,
        @Periode2 = @Periode2
    ;
END
GO

In this particular example, you're not gaining anything by using a dynamic query. You could replace the entire body of the stored procedure with the contents of your @Dynamictbl string.