Creating stored procedure using cfstoredproc

Question

Is is possible to create a stored procedure using cfstoredproc? When I run the following I get Incorrect syntax near 'GO'.

<cffile action="read" file="mypath/myFile.sql" variable="sp_1">

<cfstoredproc procedure="sp_executesql" dataSource="#getDatasource()#">
    <cfprocparam type="in" cfsqltype = "cf_sql_varchar" value ='#sp_1#'>
</cfstoredproc>

myFile.sql

IF OBJECT_ID('getMyData', 'P') IS NOT NULL
    DROP PROC getMyData
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE getMyData
    @some_var    AS NVARCHAR(200)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE  @sql AS NVARCHAR(MAX)

        SET @sql = N'SELECT * FROM myTable where id = ''' + @some_var + ''' '

    EXEC sp_executeSQL @sql 

END


<cfquery name="createGetMyData" dataSource="#getDatasource()#">
    #preservesinglequotes(sp_1)#
</cfquery>

Answer 1

Try this:

<cffile action="read" file="mypath/myFile.sql" variable="sp_1">

<cfstoredproc procedure="sp_executesql" dataSource="#getDatasource()#">
    <cfprocparam type="in" cfsqltype = "cf_sql_varchar" value ='#preservesinglequotes(sp_1)#'>
</cfstoredproc>

ColdFusion escapes your single quotes in db variables.

EDIT:

Secondly there is the batching of statements. the drive will batch your query as a single statement wheras the "GO" keyword is an indicator of a batch prepared. In other words, your "GO" actually IS the issue.

To fix it you will need to run 2 querys - one to drop and the other to create. Why? Because CREATE PROCEDURE actually has to be the first statement in a given batch. in MSSQL studio, using GO, you are creating 3 batches, now you have to figure out how to use one.

The good news is that your ANSI nulls and Quoted identifiers are probably not needed - they are defaulted on most instances.

Does this help?