views:

51

answers:

1

I'm getting this error when trying to run a query that inserts results into a table in sql. im passing the table name as parameter,how to give the hierarchy value to the insert statement.

here is my code:

declare @pathhere hierarchyid

select @pathhere=Path from SectionDetails where Sectionid=@sectionid and SectionName=@sectionname and Batchid=@batchid  and Deptid=@deptid and Schoolid=@schoolid

insert stmt:

set @sqlstmt = 'insert into '+@batch+'(StudentID, StudentName,SectionID,SectionName,BatchID,BatchName, DeptID,DeptName, SchoolID,Path)
values('''+@sectionid+''','''+@sectionname+''','''+@sectionid+''','''+@sectionname+''','''+@batchid+''','''+@batchname+''','''+ @deptid+''','''+@deptname+''', '''+@schoolid+''','+ CAST(@pathhere as hierarchyid)+')'
exec(@sqlstmt)

im getting error in this line:

'+ CAST(@pathhere as hierarchyid)+'

as Invalid operator for data type. Operator equals add, type equals hierarchyid.

can anyone pls help me out how to pass the hierarchy value

A: 

You're trying to create a string that can be executed as a statement. So you need to get your hierarchyid into nvarchar(max) instead.

try: @pathhere.ToString()

Rob Farley
but if i use this im getting Could not find method 'Tostring' for type 'Microsoft.SqlServer.Types.SqlHierarchyId' in assembly 'Microsoft.SqlServer.Types'
Ranjana
So you're replacing the part you say is causing you an error with `'+ @pathhere.ToString() +'` , right?
Rob Farley
thanks for yr reply..ya
Ranjana
What does this give you:select Path, Path.ToString() from SectionDetails where Sectionid=@sectionid and SectionName=@sectionname and Batchid=@batchid and Deptid=@deptid and Schoolid=@schoolid
Rob Farley
create procedure Copy_school(@tablename varchar(500),@id varchar(500),@name varchar(500))asbegindeclare @pathhere hierarchyiddeclare @sql varchar(max)select @pathhere= Path from SchoolDetails where Schoolid=@idset @sql='insert into '+ @tablename+'(Schoolid,SchoolName,School_Ownerid,School_OwnerName,Path)values('+@id+','+@name+','+@id+','+@name+','+@pathhere+')'exec(@sql)endi need how to pass the hierarchy value path( i.e) @pathhere to insert stmt when i pass my table name dynamically. i need the format to pass the path parameter.
Ranjana
Have a look at `select Path, Path.ToString() from SectionDetails`, and see what it shows. You want to create a string that handles stuff. Change `exec(@sql)` to `SELECT @sql` so that you can see what it's trying to run.
Rob Farley

related questions