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 |
What happens if the update subquery returns no rows in ms sql server?
explain query execution plan
What is query and its types?
How to use "begin ... End" statement structures in ms sql server?
Explain DBMS, RDBMS?
What is data integrity? Explain constraints?
Which are the olap features?
What is the difference between a local and a global variable?
What do you understand by recursive stored procedure?
How to concatenate two binary strings together?
What are the differences between substr and charindex in sql server.
What is the fillfactor concept in indexes?