Dynamic sql if exists drop andcrete funcation or procedures

Transact-SQL
1

Hi

I have requirements like this need to create dynamic sql for procedures and functions like this

DECLARE @sub_create_store_procedure nvarchar(max)

SET @sub_create_store_procedure = '
IF OBJECT_ID (N''dbo.fs_test'') IS NOT NULL

DROP FUNCTION dbo.fs_test

CREATE FUNCTION [dbo].[fs_test] (@STR nvarchar(max))

RETURNS nvarchar(max)

AS

BEGIN

RETURN REPLACE(REPLACE(@STR,''['',''''),'']'','''');

END

'

EXEC sp_execu

getting below error
Msg 111, Level 15, State 1, Line 8
'CREATE FUNCTION' must be the first statement in a query batch.
Msg 137, Level 15, State 2, Line 16
Must declare the scalar variable "@STR".

2

Why do you need it to be dynamic?

3

Need to save all the stored procedures, functions and views into the table whenever required to deploy to new Dbs need to retrieve from the table and deploy. This is requirement

we have suggested another way but they are not accepting

4

So you guys dont use gitlab or some source code repository ???

Craziest thing I have ever heard

5

You need to re-think the design - you probably need to issue the DROP statement in one batch and then the create in another batch. An alternative would be to use 'CREATE OR ALTER' instead, that way you don't need the DROP statement.

This is also going to become a nightmare to manage and maintain - just having to escape single-quotes will be problematic, and any significantly complex stored procedure will be absolutely impossible to test and validate prior to deployment.

If this was something that I had to create (which I wouldn't - not at all) - I would create the code as normal, script out the function/procedure using SMO (most likely) and then store that generated script to the 'deployment' table.

6

So pretty much they want to emulate gitlab, this is just wild