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 |
Write down the syntax and an example for create, rename and delete index?
explain what are the steps you will take, if you are tasked with securing an sql server? : Sql server database administration
when we use function instead of procedure? plz tell me the situation with ex?
Where is SQL Srever (In sQL server 2005/2008 where is SQL Server Located).
What is service broker?
How to get nth highest salary from employee table.
Mention the differences between having and where clause.
optimization techinques
Explain the various types of concurrency problem?
What is self contained multi valued query?
Do you know what is sql injection?
Psudo colums