This is an offshoot of the solution suggested in BOL under the "hierarchical information" heading....

SET NOCOUNT ON

CREATE TABLE #TabA(Name Varchar(10),ID int)

CREATE TABLE #TabB(ChildID int,ParentID int)

CREATE TABLE #Stack(ID int,Level int)

CREATE TABLE...

You can't create views on temporary tables...you'll have to either

a) wrap up the code above inside a stored procedure and execute the stored procedure to get the results..

or

b)re-write the...

We used this to attach results of @query as a csv :

exec master.dbo.xp_sendmail @recipients = 'EmailId',

@message = 'Results in CSV',

@query = 'Select au_id,au_lname,au_fname from pubs.dbo.authors',

@attach_results = 'TRUE',

@attachments = 'Authors.csv',

@separator =...

Hi Shifan,

To modify the trigger --

Open SQL Enterprise Manager

Right click on the table on which the trigger has been created

Select the 'All Tasks' menu option and then the 'Manage Triggers'...

This might work....

Declare @TextPtr varbinary(16)

Declare @StartPosition Int

SET @StartPosition = 1

SELECT @TextPtr = TEXTPTR(TextColumnName) FROM TableName WHERE <WHERE CLAUSE>

WHILE @StartPosition > 0

BEGIN

Select @StartPosition = Charindex(Char(13),TextColumnName)-1 -- same for Char(10)???

...

This is one way to go about it....

Declare @RetVal Varchar(1000)

SET @RetVal = ''

SELECT @RetVal = @RetVal+','+(Select Min(RoleName) from Roles where Roles.RoleName >= Outer1.RoleName) from Roles Outer1

SELECT Substring(@RetVal,2,Len(@RetVal))

-- eliminate first ','

Hi,

you could also check out

http://www.dbajobs.com/database_job_titles.htm

ahhh...now thats a simple solution...

this works since you have put the sort order out of the CASE statement...previously adding the "asc" or "desc" required that...

"mi" indicates the difference between current date and the value of the date field in minutes (which is used to order the dates in ascending or descending order)...

you could also...

This might help :

ORDER BY

CASE @OrdSeq WHEN 1 THEN customername END,

CASE @OrdSeq WHEN 2 THEN customerpo END,

CASE @OrdSeq WHEN 3 THEN status END,

CASE @OrdSeq WHEN 4 THEN fe END,

CASE @OrdSeq...

w.r.to the original stored procedure query --I'd try to avoid the dynamic Sql solution and go with the charindex approach...that would perform better IMO...

I've learnt other approaches in this forum...

true...the SQL statement that you are assigning to a variable and printing out will run in query analyser...to get that to work within your stored procedure you need to use...

Hi Shailesh,

As Steve Jones said - the IN doesn't work properly with string variables...

Consider the following :

SET NOCOUNT ON

create table #Test1(ItemId Varchar(10))

Insert #Test1(ItemId) Values('a')

Insert #Test1(ItemId) Values('b')

Insert #Test1(ItemId) Values('c')

Insert #Test1(ItemId) Values('d')

Declare...

You could also use charindex to obtain results in this case...

Assuming that the table structure is something like

TableName(Itemid int,Itemname varchar(20))

Declare @itemids varchar(20)

SET @itemids = '1,2,3,4'

SET @itemids = ','+@itemids+','

Select * from...

Hi,

Are you trying to print out the various BranchCodes within the cursor??

you might try replacing the sp_executesql with the following :

Set @SQLString = 'Branch = ' + '''' + @BR...