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 to verify a login name with sqlcmd tool?
What are different types of replication in sql server?
Why do we use functions?
How to read data in a table with "select" statements?
Explain nested join?
What are the OS services that the SQL Server installation adds?
What is the difference between DataRow.Delete() and DataRow.Remove()?
Explain about remote stored procedure?
How can you insert null values in a column while inserting the data?
Difference between group by clause and having clause in SQL?
How to create a user to access a database in ms sql server using "create user" statements?
Explain in brief about Microsoft SQL server?