Results 1 to 10 of 10
  1. #1
    Join Date
    May 2004
    Location
    PA
    Posts
    7

    Question Unanswered: T-SQL Syntax Error (conversion

    Hi,

    New to the forum here, it seems like this is a good SQL Server resource...

    I'm still somewhat in the learning phases of T-SQL coding, and so far what I've learned has been pretty beneficial...a problem i'm having at the moment though, is with some conversion. I created a stored procedure that basically takes all the records from one table to another. I'll paste the code onto here and show you what's flagging on me (comment about the error within code). It probably seems a bit convoluted, but the UserProfileValue table is something that I TOTALLY wish Microsoft had second-guessed (it's from SharePoint Portal Server 2003)

    Code:
    SET QUOTED_IDENTIFIER ON 
    GO
    SET ANSI_NULLS ON 
    GO
    
    
    ALTER  procedure profile_ImportUserProfileData
    
    as
    
    declare profile_curs cursor for 
    	select UserProfileValue.RecordID, UserProfileValue.PropertyID, UserProfileValue.PropertyVal, PropertyList.PropertyName, PropertyList.DataType
    		from PropertyList 
    		inner join UserProfileValue 
    		on PropertyList.PropertyID = UserProfileValue.PropertyID
    		order by UserProfileValue.RecordId,
    			 UserProfileValue.PropertyId
    
    declare @RecordId int, @PropertyId int, @OldRecordId int
    declare @PropertyVal sql_variant
    declare @PropertyName nvarchar(20), @DataType nvarchar(20)
    declare @table nvarchar(25)
    
    set @table = 'UserProfileReportTable'
    
    open profile_curs
    fetch next from profile_curs into @RecordId, @PropertyId, @PropertyVal, @PropertyName, @DataType
    
    if (@@fetch_status = -1)
    begin
    	print 'End of table'
    	close profile_curs
    	deallocate profile_curs
    	return
    end
    
    while (@@fetch_status = 0)
    begin
    	SET IDENTITY_INSERT UserProfileReportTable ON
    	insert UserProfileReportTable(RecordId)
    		values(@RecordId)
    	SET IDENTITY_INSERT UserProfileReportTable OFF 
    
    	set @OldRecordId = @RecordId
    
    	fetch next from profile_curs into @RecordId, @PropertyId, @PropertyVal, @PropertyName, @DataType 
    	
    	if @DataType like 'nvarchar'
    		set @DataType = @DataType + '(20)'
    	
    	while (@RecordId = @OldRecordId)
    	begin
    		declare @sql nvarchar(1000)
    		set @sql = 'update ' + @table
                            -- this next line is the one that's crapping out, saying "Incorrect syntax near @DataType"
    		set @sql = @sql + ' set ' + @PropertyName + ' = ' + convert(@DataType, @PropertyVal) 
    		set @sql = @sql + ' where RecordId = ' + @RecordId
    		execute sp_executesql @sql
    		fetch next from profile_curs into @RecordId, @PropertyId, @PropertyVal, @PropertyName, @DataType
    	end
    end
    
    close profile_curs
    deallocate profile_curs
    
    
    GO
    SET QUOTED_IDENTIFIER OFF 
    GO
    SET ANSI_NULLS ON 
    GO
    I also tried cast(@PropertyVal as @DataType) but got the same results. I know it probably isn't the best idea to use a cursor, considering there are 3000+ records in UserProfileValue, but more than likely this procedure will only be run once, and I couldn't think of any better way to do it. Any help would be appreciated.

    Thanks!

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    @sql = @sql + ' set ' + @PropertyName + ' = ' + convert(' + @DataType+ ',' + @PropertyVal+')'
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    May 2004
    Location
    PA
    Posts
    7
    Thanks, that seemed to solve that problem, though I had to adjust it somewhat...

    set @sql = @sql + ' set ' + @PropertyName + ' = convert(' + @DataType + ',' + @PropertyVal + ')'

    I dont think I mentioned before that the @PropertyVal variable is a sql_variant, and the new error I'm getting is this: "Invalid operator for data type. Operator equals add, type equals sql_variant." I had gotten this before, and tried to research it online but help was to no avail...any ideas???

  4. #4
    Join Date
    Feb 2004
    Location
    Poland
    Posts
    32
    Quote Originally Posted by erickson82
    Thanks, that seemed to solve that problem, though I had to adjust it somewhat...

    set @sql = @sql + ' set ' + @PropertyName + ' = convert(' + @DataType + ',' + @PropertyVal + ')'

    I dont think I mentioned before that the @PropertyVal variable is a sql_variant, and the new error I'm getting is this: "Invalid operator for data type. Operator equals add, type equals sql_variant." I had gotten this before, and tried to research it online but help was to no avail...any ideas???
    Why not CAST @PropertyVal? You try to concatenate it to string and this is SQL_VARIANT. Conversion of sql_variant to all other types (except timestamp, text, ntext, image) is explicit, and explicit conversions are those conversions that require the CAST (CONVERT) function to be specified.

  5. #5
    Join Date
    May 2004
    Location
    PA
    Posts
    7
    You mean like this..?

    set @sql = @sql + ' set ' + @PropertyName + ' = cast(' + @PropertyVal + ' as ' + @DataType + ')'

    I tried it before and got the same error.

  6. #6
    Join Date
    Feb 2004
    Location
    Poland
    Posts
    32
    Quote Originally Posted by erickson82
    You mean like this..?

    set @sql = @sql + ' set ' + @PropertyName + ' = cast(' + @PropertyVal + ' as ' + @DataType + ')'

    I tried it before and got the same error.
    Nope.

    I suppose @sql is varchar or nvarchar. Look what you concatenate:
    @sql - same type (ok)
    ' set ' - string (ok)
    @PropertyName - suppose it's varchar/nvarchar, so ok.
    ' = cast(' - string (ok)
    @PropertyVal - it's SQL_VARIANT - and this makes error. You try to concatenate string and SQL_VARIANT(which doesn't have implicity conversion). If you want to use value from @PropertyVal variable, you have to convert it.
    ' as ' - string (ok)
    @DataType - suppose it's varchar/nvarchar, so ok.
    ')' - string (ok)

  7. #7
    Join Date
    May 2004
    Location
    PA
    Posts
    7
    Thanks..I think I got that part fixed up, the procedure ran without a hitch...the resulting syntax went like this:

    set @sql = @sql + ' set ' + @PropertyName + ' = cast(' + cast(@PropertyVal as nvarchar(2000)) + ' as ' + @DataType + ')'

    .....yeah, pretty long line there...

    one more problem now...doesn't have to do with conversion, but when I actually try executing the entire stored procedure (it totals about 70+ lines), I get errors saying that the PropertyName field in the table I'm importing the data into isn't right..it almost seems as if it's trying to use the actual data as column names...

    Does the above SET statement somehow assign the @PropertyVal variable value to @PropertyName??

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    erickson82, I love your signature quote. You'll fit in well here.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Feb 2004
    Location
    Poland
    Posts
    32
    Quote Originally Posted by erickson82
    (...)
    set @sql = @sql + ' set ' + @PropertyName + ' = cast(' + cast(@PropertyVal as nvarchar(2000)) + ' as ' + @DataType + ')'
    (...)
    Does the above SET statement somehow assign the @PropertyVal variable value to @PropertyName??
    Try after the SET line add PRINT @sql, so you can see prepared query. If still can't find error post here this query, both with error message.

    INSERT INTO T_Cup SELECT coffee, sugar, hot_water, milk
    EXEC sp_stir
    EXEC sp_drink
    GO

  10. #10
    Join Date
    May 2004
    Location
    PA
    Posts
    7
    Thanks! Your suggestion pointed the way to my error and then I finally developed a working solution. Since the value in the cast() function did not have any single quotes around it, it must have been interpreted as a field name, therefore that's where the errors were flying. After adding single quotes within the string literal, it looks like this:

    set @sql = @sql + ' set ' + @PropertyName + ' = cast(''' + cast(@PropertyVal as nvarchar(2000)) + ''' as ' + @DataType + ')'

    Here's the final product:

    Code:
    SET QUOTED_IDENTIFIER OFF 
    GO
    SET ANSI_NULLS ON 
    GO
    
    /***This procedure imports the values from the UserProfileValue table into a new table called
        UserProfileReportTable, the purpose being that it's much more readable and far better for
        the sake of reporting.  modified by tje 5/19/2004***************************************/
    
    ALTER    procedure profile_ImportUserProfileData
    
    as
    
    declare profile_curs cursor for 
    	select UserProfileValue.RecordID, UserProfileValue.PropertyID, UserProfileValue.PropertyVal, PropertyList.PropertyName, PropertyList.DataType
    		from PropertyList 
    		inner join UserProfileValue 
    		on PropertyList.PropertyID = UserProfileValue.PropertyID
    		order by UserProfileValue.RecordId,
    			 UserProfileValue.PropertyId
    
    declare @RecordId int, @PropertyId int, @OldRecordId int
    declare @PropertyVal sql_variant, @PropertyName nvarchar(20), @DataType nvarchar(20)
    declare @table nvarchar(25) 
    
    set @table = 'UserProfileReportTable'
    
    open profile_curs
    fetch next from profile_curs into @RecordId, @PropertyId, @PropertyVal, @PropertyName, @DataType
    
    if (@@fetch_status = -1)
    begin
    	print 'End of table'
    	close profile_curs
    	deallocate profile_curs
    	return
    end
    
    while (@@fetch_status = 0)
    begin
    	SET IDENTITY_INSERT UserProfileReportTable ON
    	
    	--inserts new row, filling only RecordId column for now
    	insert UserProfileReportTable(RecordId)
    		values(@RecordId)
    	
    	SET IDENTITY_INSERT UserProfileReportTable OFF 
    
    	--for use in while loop
    	set @OldRecordId = @RecordId
    
    	if @DataType = 'nvarchar'
    		set @DataType = @DataType + '(1000)'
    	
    	/*loops through the UserProfileValue table while the 
    	  RecordId is still the same as the one that was just 
    	  inserted into the new table, filling in the data 
    	  associated with that particular record*/
    
    	while (@RecordId = @OldRecordId)
    	begin
    		declare @sql nvarchar(4000)
    		set @sql = 'update ' + @table
    		set @sql = @sql + ' set ' + @PropertyName + ' = cast(''' + cast(@PropertyVal as nvarchar(2000)) + ''' as ' + @DataType + ')'
    		set @sql = @sql + ' where RecordId = ' + cast(@RecordId as nvarchar(10))
    		execute sp_executesql @sql
    		fetch next from profile_curs into @RecordId, @PropertyId, @PropertyVal, @PropertyName, @DataType
    		
    		if (@@fetch_status = -1)
    			break		
    		
    		print @sql
    	end
    end
    
    close profile_curs
    deallocate profile_curs
    
    
    
    GO
    SET QUOTED_IDENTIFIER OFF 
    GO
    SET ANSI_NULLS ON 
    GO
    Feel free to criticize (...constructively, that is)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •