CODEWITHSUNDEEP
sqlsql-server-2008stored-proceduresconcatenation
Artur
Artur

Reputation: 343

Pass string variable into procedure and add it to a query

I have an application written in C#, which connects to database and analyze its data, database stores information about execution of automated tests, what I would like to do is to retrieve those tests that fulfill the above given conditions. But we are having different projects and will be supporting more and more so I do not want to construct different procedure for each one, but pass the name - 2nd parameter deploy as the parameter so the query will depend on the project and return the data to the application, then I will send it in a report.

For the time being it looks like this:

CREATE PROCEDURE [dbo].[SuspectsForFalsePositive](@build_id INT, @deploy VARCHAR(25)) 
AS
BEGIN

    SET NOCOUNT ON;

    DECLARE @i int, @build int, @deployname varchar(25), @SQL varchar(max)
    DECLARE @result table (tc int, fp float)
    SET @i = 0
    SET @build = @build_id
    SET @deployname = @deploy

    SET @SQL = 'insert '+@result+'select testcase_id, fail_percentage FROM [BuildTestResults].[dbo].['+@deployname+'TestCaseExecution]
                   where build_id = @build and fail_percentage >= 70'

--INSERT @result select testcase_id, fail_percentage FROM [BuildTestResults]
--.[dbo].[ABCTestCaseExecution]
--where build_id = @build and fail_percentage >= 70
--commented works
    EXEC(@SQL)
    WHILE (@@rowcount = 0)
    BEGIN
    SET @build = @build - 1
    EXEC(@SQL)
--INSERT @result select testcase_id, fail_percentage FROM [BuildTestResults].[dbo]. --[ABCTestCaseExecution]
--where build_id = @build and fail_percentage >= 70
--commented works
    END
    select * from @result order by fp DESC
END
GO

Thanks for any advice !

Upvotes: 0

Views: 6012

Answers (2)

MatBailie
MatBailie

Reputation: 86706

There are a few issues with your example. This is, however, one over-arching consideration.

Variables (table and/or scalar) are only visible in the StoredProcedure they are defined in. And calling EXEC(@SQL) is calling a stored. This means that neither your @result table, not your other parameters are visible to the dynamic SQL you are executing.

In terms of the table, you can get around that by creating a temp table instead. And for the scalar variables, you can pass them around when using SP_EXECUTESQL instead of EXEC.

I don't have access to sql server at present, but maybe somethign like this can start you on your way...

CREATE PROCEDURE [dbo].[SuspectsForFalsePositive](@build_id INT, @deploy VARCHAR(25)) 
AS
BEGIN

  SET NOCOUNT ON;

  DECLARE
    @i          int,
    @build      int,
    @deployname varchar(25),
    @SQL        varchar(max)

  CREATE TABLE #result (
    tc int,
    fp float
  )

  SELECT
    @i          = 0,
    @build      = @build_id,
    @deployname = @deploy

  SET @sql = ''
  SET @sql = @sql + ' INSERT INTO #result'
  SET @sql = @sql + ' SELECT testcase_id, fail_percentage'
  SET @sql = @sql + '   FROM [BuildTestResults].[dbo].['+@deployname+'TestCaseExecution]'
  SET @sql = @sql + '  WHERE build_id = @build and fail_percentage >= 70'

  SP_EXECUTESQL
     @SQL,
     '@build INT',
     @build

  WHILE (@@rowcount = 0)
  BEGIN
    SET @build = @build - 1
     SP_EXECUTESQL
       @SQL,
       '@build INT',
       @build
  END

  SELECT * FROM #result ORDER BY fp DESC

END
GO


I also occures to me that @@rowcount may now see the rows being processes within SP_EXECUTESQL. In which case you may need to re-arrange things a little (using an output parameter, or embedding the loop in the @SQL, etc).


Overall it feels a bit clunky. With more information about your schema, etc, it may be possible ot avoid the dynamic SQL. This will have several benefits, but one in particular:
- Right now you're open to SQL Injection Attacks on the @deploy parameter

Anyone that can execute this SP, and/or control the value in the @deploy parameter could wreak havok in your database.


For example... Could you store all the TestCaseExecutions in the same table? But with an extra field: TestCaseID *(Or even TestCaseName)?

Then you wouldn't need to build dynamic SQL to control which data set you are processing. Instead you just add WHERE TestCaseID = @TestCaseID to your query...

Upvotes: 0

Oded
Oded

Reputation: 498914

In your string you have @build - this is interpreted as a string. At the time you execute the @SQL it doesn't contain such a variable, so you get a failure.

You need to concatenate the value directly:

SET @SQL = 'insert '+@result+'select testcase_id, fail_percentage FROM [BuildTestResults].[dbo].['+@deployname+'TestCaseExecution]
                   where build_id = '+@build+' and fail_percentage >= 70'

You will need to do that between executions too.

Upvotes: 1

Related Questions