quote:

---

Originally posted by thiyait



My need of call is getting the query like this

i needed the query like this so i call the another function to retrive data
Select @userIds = isnull(@userIds+',''','''') + convert(varchar,userId) + '''' from userroles where roleid in ('8','9','10' )

Select @userIds = isnull(@userIds+',''','''') + convert(varchar,userId) + '''' from userroles where roleid in dbo.GetuserTempid(@PortalId,@userid,@tabid)........this is calling fucntion


My coding follows:

First Function 1:
CREATE FUNCTION dbo.getfinalresult (@portalid as int ,@userid as int ,@tabid as int)
RETURNS nvarchar(100) AS
BEGIN
declare @tempauth nvarchar(50)
declare @id as int
declare @userIds varchar(100)
Select @userIds = isnull(@userIds+',''','''') + convert(varchar,userId) + '''' from userroles where roleid in dbo.GetuserTempid(@PortalId,@userid,@tabid)
Select @tempauth= '('+@userIds+')'
return @tempauth
END



second function 2:

CREATE function GetuserTempid(@PortalId int,@userid int,@tabid int )
returns nvarchar(50) as
begin
declare @tempuserid as nvarchar(100)
set @Tempuserid =( SELECT '(''' + REPLACE(LEFT(LTRIM(RTRIM(authorizedroles)), LEN(LTRIM(RTRIM(authorizedroles)))-1), ';', ''',''') + ''')' FROM tabs WHERE tabid = @tabid)
return @tempuserid /*Tabs table return the query like this :('4','5','5')
end

Note:

Table tabs contain:

select authorizedroles from tabs WHERE tabid = @tabid

result:
authorizedroles
8
9
10

but i needed format is ('8','9','10')
Function two return the above specified format

Select @userIds = isnull(@userIds+',''','''') + convert(varchar,userId) + '''' from userroles where roleid in dbo.GetuserTempid(@PortalId,@userid,@tabid)/* show the syntax error*/


Guys any body help me.......

---