mercredi 23 mars 2016

Store a generated SQL String into a table via Stored Procedure

I have an application where a user fills out a form which in turn generates a pretty lengthy Query. Well as part of a usage statistics program, I track and log everything our users do in one spot in the database. I have a stored procedure that inserts the appropriate values into this database, however the issue I'm running into is inserting the actual Query that took place.

Stored Procedure

    USE [Worktool]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Insert_Usage_Statistics]
    -- Add the parameters for the stored procedure here
    @Query_VC AS VARCHAR(MAX) = NULL

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    INSERT INTO Usage_Statistics_T
        (
            Query_VC

        ) 
        VALUES 
        (
            @Query_VC

        )
END

Example of it being called

    exec Worktool.dbo.Insert_Usage_Statistics  

@Query_VC = 'SELECT col1 FROM tbl1  INNER JOIN tbl2 ON tbl1.id = tbl2.id WHERE       tbl1.id IN ('1','2')'

The error I'm receiving is around the IN ('1','2') syntax.

Aucun commentaire:

Enregistrer un commentaire