0

I have a stored procedure:

CREATE PROCEDURE [dbo].[sp_ToChangeVersion]
  (@NewVersion nvarchar(100),
   @ScriptFile nvarchar(max))
AS
BEGIN

    DECLARE @CurrentVersion AS NVarChar(4000)

    SELECT @CurrentVersion = VersionNumber FROM VersionInformationTable 

    DECLARE @MSG AS NVarChar(2000)
    DECLARE @verDiff Int

    SET @verDiff = @CurrentVersionINT - @NewVersionINT
    
    IF (@verDiff = 0)
        BEGIN 
            PRINT 'YOU HAVE LATEST DB STRUCTURE'
        END 
    ELSE IF(@verDiff = -1) 
        BEGIN 
            EXEC SP_EXECUTESQL @Script 
            Update  VersionInformationTable  set VersionNumber = @NewVersion 
            print  'Script Executed Successfully'
        END

    ELSE
        BEGIN
                PRINT 'Pass the proper version Number'
            END
        END
    END

The purpose of this stored procedure is to update the database with a version number. I have a script file which is generated from the Delta tool (compared with another DB), which m passing as a parameter to update my database with a new version.

My question is how will I pass the .sql file as a parameter and how to execute that .sql file in the stored procedure?

Oleg Valter is with Ukraine
  • 9,619
  • 8
  • 37
  • 59
Amitha Manya
  • 1
  • Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! – marc_s Mar 16 '15 at 10:58
  • 1
    Does this have anything to do with this: http://stackoverflow.com/questions/29073276/execute-storedprocedure-by-passing-the-script-file-sql-as-parameter#comment46382015_29073276 – Nick.Mc Mar 16 '15 at 10:58
  • One word of advice: **don't do this!** Instead: you just call the stored procedure with the new **version number** (as a string, or as a number - up to you) and then update what you need to update. Don't go parsing `.sql` files inside stored procedures..... – marc_s Mar 16 '15 at 10:59

0 Answers0