Adding some text to a stored procedure

  • Hi all!

    I have made this defination for a stored procedure:

    PROCEDURE EP_Conterbalances

    @Start_Date_For_Totals_Date DATETIME,

    @EmpFilterAddDuty VARCHAR(500),

    @CounterBalanceType_id INT,

    @dateFrom DATETIME,

    @DateTo DATETIME

    AS

    BEGIN

    [Much code!] and then

    and d.employeeid IN ( @EmpFilterAddDuty)

    The value of @EmpFilterAddDuty could be:

    'SELECT E.EmployeeID FROM dbo.EmployeeGroupMapToEmployee E, dbo.Per_Budget B WHERE E.EmployeeID = B.PER_PERSONAL_ID AND B.PEB_Budget_id = 243 AND E.EmployeeGroupID IN (SELECT H.Id FROM dbo.EmployeeGroup H WHERE H.InstitutionsId = 22) GROUP BY E.EmployeeID '

    If i Replace @EmpFilterAddDuty with this in a QUERY, it gives me the expected result, but if i try to execute the stored procedure.:

    DECLARE@return_value int

    EXEC@return_value = [dbo].[EP_Conterbalances]

    @Start_Date_For_Totals_Date = N'20120831',

    @EmpFilterAddDuty = 'SELECT E.EmployeeID FROM dbo.EmployeeGroupMapToEmployee E, dbo.Per_Budget B WHERE E.EmployeeID = B.PER_PERSONAL_ID AND B.PEB_Budget_id = 243 AND E.EmployeeGroupID IN (SELECT H.Id FROM dbo.EmployeeGroup H WHERE H.InstitutionsId = 22) GROUP BY E.EmployeeID ',

    @CounterBalanceType_id = 4,

    @dateFrom = N'20150601',

    @DateTo = N'20150630'

    SELECT'Return Value' = @return_value

    I get this error code:

    Conversion failed when converting the varchar value 'SELECT E.EmployeeID FROM dbo.EmployeeGroupMapToEmployee E, dbo.Per_Budget B WHERE E.EmployeeID = B.PER_PERSONAL_ID AND B.PEB_Budget_id = 243 AND E.EmployeeGroupID IN (SELECT H.Id FROM dbo.EmployeeGroup H WHERE H.InstitutionsId = 22) GROUP BY E.EmployeeID ' to data type int.

    I really do not understand why SQL 2012 tries to convert the value to an int, and I want to know how to pass the text string.

    Best regards

    Edvard Korsbæk

  • You cannot pass T-SQL to be executed like this.

    You will have to either resort to using something like sp_executesql, or change your proc around a bit to avoid this somewhat ungainly construction.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • As mentioned, you need to use dynamic SQL, but with your current code, you're wide open for SQL Injection. Here's an example on how could you do it.

    CREATE PROCEDURE EP_Conterbalances

    (

    @Start_Date_For_Totals_Date DATETIME,

    @EmpFilterAddDuty NVARCHAR(500), --sp_Executesql requires unicode strings

    @CounterBalanceType_id INT,

    @dateFrom DATETIME,

    @DateTo DATETIME

    )

    AS

    BEGIN

    --[Much code!] and then

    IF OBJECT_ID( 'tempdb..#EmpIDFilter') IS NOT NULL

    DROP TABLE #EmpIDFilter

    CREATE TABLE #EmpIDFilter(EmployeeID int)

    INSERT INTO #EmpIDFilter

    EXEC sp_executesql @EmpFilterAddDuty --Be prepared for SQL Injection, beware of Bobby Tables

    --Your query with the problematic clause.

    and d.employeeid IN ( SELECT EmployeeID FROM #EmpIDFilter)

    --Just to make it nice

    DROP TABLE #EmpIDFilter

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Thanks Both!

    Always learning....

    And I have never ever found out myself.

    I do not think, that SQL injection is a problem here.

    I am not working on the net - This is a WIN32 program used in a controlled enviroment.

    Will make a small sample first, and then put it into 'Real world.

    Best and quite humble regards

    Edvard Korsbæk

  • Just remember that if there's human input, there's a threat. It doesn't matter if it comes from outside or inside of the company. Protect yourself against any way that a user can input a direct string.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply