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 are the restrictions that views have to follow? : SQL Server Architecture
how to retrive only second row from table in sql server 2000?
What is fill factor and pad index?
What are the different types of replication you can set up in sql server?
What is the difference between a Application Server and a Database
What is the difference between WHERE AND IN? OR 1. SELECT * FROM EMPLOYEE WHERE EMPID=123 2. SELECT * FROM EMPLOYEE WHERE EMPID IN (123) WHAT IS THE DIFFERENCE?
What is GUID in sql server?
What is a bit datatype?
Explain Normalization and DE normalization
Explain trigger classes i.e. Instead of and after trigger?
How column data types are determined in a view?
can you any body tell me while running BCP Out in instance in sql server 2000 is getting error. Error = [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.?