WRITE A FUNCTION TO DISPLAY THE OUTPUT OF AN EXISTING TABLE
RANGE LIKE COMMAM SEPERATED VALUES LIKE RANGE1,RANGE2,...
Answers were Sorted based on User's Feedback
Answer / carmel franco
Create function int select_dynamic(range varchar(255))
As
declare
@Qry Varchar(2000)
Begin
Begin try
Set @Qry =”select * from table1 where col1
in (“+range+”)”
Exec @qry
Catch
Print “error processing parameter”
Return -1
End try
Return 1
END
Is This Answer Correct ? | 2 Yes | 0 No |
Answer / jk.garg25
you can write this query in table valued function
DECLARE @RangeValue As VARCHAR(MAX)
SET @RangeValue = '1,2,'
DECLARE @Qry As VARCHAR(MAX)
DECLARE @ResData AS VARCHAR(MAX)
DECLARE @InData AS VARCHAR(MAX)
DECLARE @rId AS INT
DECLARE @Pos AS INT
SET @ResData = ''
SET @InData = ''
WHILE(LEN(@RangeValue)>1)
BEGIN
SET @Pos = CHARINDEX(',',@RangeValue,1)
SET @rId = SUBSTRING(@RangeValue,1,@Pos-1)
SET @ResData = @ResData + CASE WHEN @ResData <> '' THEN ','
ELSE '' END + 'ISNULL(['+ CAST(@rId AS VARCHAR(5)) + '],0)
AS Range' + CAST(@rId AS VARCHAR(5))
SET @InData = @InData + CASE WHEN @InData <> '' THEN ','
ELSE '' END + '['+ CAST(@rId AS VARCHAR(5)) + ']'
SET @RangeValue=SUBSTRING(@RangeValue,@Pos+1,LEN(@RangeValue))
END
SET @Qry =
'SELECT '
+ @ResData +
' FROM
(
SELECT Col1 FROM table1(NoLock)
) p
PIVOT
(
MAX (Col1) FOR Col1 IN (' + @InData + ')
) AS pvt'
--PRINT (@Qry)
EXEC (@Qry)
Is This Answer Correct ? | 1 Yes | 0 No |
How you can minimize the deadlock situation?
List down some advantages of sql stored procedure?
Determine when to use stored procedure to complete sql server tasks?
What is the default fill factor value?
A table contains list of customers and his city with other details. Each customer has a unique number and the table consists millions of data. Query is: I want to retrieve 10 customers from each city, no script, only from single query?
What is the difference between views and stored procedures? Can we have input parameters for views?
How to select some specific columns from a table in a query in ms sql server?
What are the type of joins? When do we use Outer and Self joins?
Explain active/passive and active/active cluster configurations?
Data table as parameter in sql server?
Explain boyce and codd normal form(bcnf)?
Give an example of why you would want to denormalize a database