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



WRITE A FUNCTION TO DISPLAY THE OUTPUT OF AN EXISTING TABLE RANGE LIKE COMMAM SEPERATED VALUES LIK..

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

WRITE A FUNCTION TO DISPLAY THE OUTPUT OF AN EXISTING TABLE RANGE LIKE COMMAM SEPERATED VALUES LIK..

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

Post New Answer

More SQL Server Interview Questions

Write down the syntax and an example for create, rename and delete index?

0 Answers  


explain what are the steps you will take, if you are tasked with securing an sql server? : Sql server database administration

0 Answers  


when we use function instead of procedure? plz tell me the situation with ex?

3 Answers   IBM,


Where is SQL Srever (In sQL server 2005/2008 where is SQL Server Located).

2 Answers   TCS,


What is service broker?

0 Answers  


How to get nth highest salary from employee table.

0 Answers  


Mention the differences between having and where clause.

0 Answers  


optimization techinques

0 Answers   Wipro,


Explain the various types of concurrency problem?

0 Answers  


What is self contained multi valued query?

0 Answers  


Do you know what is sql injection?

0 Answers  


Psudo colums

1 Answers   HP,


Categories