How to pass parameters dynamically

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.

Advertisements

Table Row in String

How to create the row data as string.

If you have table row data and you need to show all the records in a single string, use the following query.

DATA:
--------------------
Name
RAM
SHYAM
HARI
RITA

RESULT:
---------------------
Name
RAM,SHYAM,HARI,RITA

Use the following query
------------------------------------
DECLARE @strList varchar(100)
SELECT @strList = COALESCE(@strList + ', ', '') +
 CAST(Name AS varchar(5))
FROM TABLE1

SELECT @strList

There are complex example in internet but you can use this simple query to get this solutions. No need to use complex cursor to get such result.