How to pass parameters dynamically?
MS-SQL has many features to call the procedure from other application and from SQL itself, See the example.
Some time you need optional parameter in Procedure then assign null as default so that it will not ask you compulsory.
CREATE PROCEDURE dbo.TESTPROCEDURE
@param1 VARCHAR(32) = NULL,
@param2 INT = NULL
AS
BEGIN
SET NOCOUNT ON
SELECT Param1 = COALESCE
(
@param1,
'@param1 was Blank'
)
SELECT Param2 = COALESCE
(
RTRIM(@param2),
'@param2 was Blank'
)
END
GO
EXEC dbo.TESTPROCEDURE @param1='HELLO', @param2=1
EXEC dbo.TESTPROCEDURE @param1='HELLO'
EXEC dbo.TESTPROCEDURE @param2=1
EXEC dbo.TESTPROCEDURE 'HELLO',1
EXEC dbo.TESTPROCEDURE 'HELLO
There are so many way to get the result from procedure, Choose any of them which you like.
Filed under: Parameters dynamically | Tagged: Best way to call procedure, COALESCE, How pass parameters dynamically, MSQL tips, Optional parameter in SQL, SQL Server | Leave a comment »
SQLHELP 