Pass string variable into procedure and add it to a query

Question

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 !

How are the "projects" stored in the database? Different tables? — Oded
– Oded, Commented Apr 12, 2012 at 11:32
I suggest you read The curse and blessings of dynamic SQL by Erland Sommarskog. — Oded
– Oded, Commented Apr 12, 2012 at 12:48

Oded · Accepted Answer · 2012-04-12 10:54:14Z

1

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.

answered Apr 12, 2012 at 10:54

Oded

501k102 gold badges900 silver badges1k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Artur Over a year ago

hello, I forgot about this, but there is still a problem with result and deployname.

Oded Over a year ago

@Artur - They will be similar issues. Can you please explain (in your question), exactly what you are trying to achieve? Instead of giving us your solution (which may be inappropriate), explain the problem you are having and which this is supposed to solve - we may suggest a different, better approach.

Artur Over a year ago

yes of course, so the problem is following - 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.

Oded Over a year ago

@Artur - Please edit your question and add the details there. A comment is not a good place for this information.

MatBailie · Accepted Answer · 2012-04-12 11:43:46Z

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

Collectives™ on Stack Overflow

Pass string variable into procedure and add it to a query

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related