January 30, 2009 at 7:19 am
Dear All,
I need to create a .SQL file with parameters...
Can i add two parameters?
I need to Create that SQL File in such a way that based on these two values certain scripts alone must be executed...
For Eg :
Let the Parameters me P1 & P2
If P1 < P2
Execute Set of Scripts
end if
if P1 > P2
Execute Set of Scripts
end if
Please help me ...
Your help is highly appreciated
My Environment :: .NET 3.5 with SQL Server Express 2005
Regards
Vathan
January 30, 2009 at 7:29 am
January 30, 2009 at 10:54 am
Dear Bitbucket,
In that scenario there might be some Scripts....
Regards
Vathan
January 31, 2009 at 9:31 am
Hoping that this is what you are looking for. Create a parameterized stored procedure and then call that procedure passing your input values. Note in the sample I elected to define the passed parameters as integers, as long as they are the same data type they could be decimal, character, varchar money, date/time etc. You would of course replace the trivial print statement with the script you need. In your net application besides declaring the stored procedure name, you will have to create 2 parameters, and assign values to them before executing the procedure.
CREATE PROCEDURE Dbo.AlphaIFTest
@P1 INT,
@P2 INT
AS
IF @P1 < @P2
BEGIN
PRINT 'p1 less than p2'
END
IF @P1 > @P2
BEGIN
PRINT 'P1 greater than p2'
END
IF @P1 = @P2
BEGIN
PRINT 'P1 equals P2'
END
/* Testing
dbo.alphaiftest 1,2
GO
dbo.alphaiftest 20,10
GO
dbo.alphaiftest 100,100
*/
February 1, 2009 at 11:48 pm
Dear Bit Bucket,
Thanks for your reply...
But my requirement is i just need a .SQL file which should accept paramaters...
Regards
Vathan
February 2, 2009 at 2:18 am
can you tell me how are you thinking of passing parameters to text file (.sql ext)? sql file is not an exe to which you can pass parameters but it is a script file which just contains some script to execute certain sql commands.
the only way to execute some script is to pass parameters through some stored procedure and then depends on your condition you can execute certain script.
hope you'll understand your requirement better or will help me understand your problem.
February 2, 2009 at 2:44 am
Hi,
You can do that in .NET or VB.... Just use FILESTREAM methods to read that file. Use some predefined text pattern in the file and replace in your VB or .NET code.
For example:
Let the file content of .sql file be
SELECT * FROM {tblnamehere} WITH (NOLOCK)
Now you read this line into your application. Use replace command to replace {tblnamehere} with the parameter you would like to pass and then output the SQL... Hope this answers your query.
Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply