如何在transct sql查询中将参数值传递给函数 [英] How to pass parameter value to function in transct sql query

问题描述

 SET @SQLQuery = 
'使用T AS 
（
选择ROW_NUMBER （）Over（Order By'+ @Sort +'）作为ROWNUMBER，D。*，Count（*）over（）作为TotalRecords FROM 
（
选择
 ld.LocationSugDistanceId，
 ld.FromLocationId，
 ulbt.Name作为FromLocation，
 tl.Name作为ToLocation，
 ld.Distance，
 d.DivisionId，
 d.Name as分部，
 e.FirstName as SuggestedBy 
 
 
来自
 LocationSuggested ld 
 --join位置fl on ld.FromLocationId = fl.LocationId 
加入位置tl on ld.ToLocationId = tl.LocationId 
 left join Division d on ld.DivisionId = d.DivisionId 
 join F_UserLocationByType（'+ @UserId +'，'+ @ LocationType +'）ulbt在ld.FromLocationId = ulbt.LocationId 
加入员工e在ld.CreatedBy = e.EmployeeID 
）D其中（1 = 1）'

以上例如，当我运行这个时，我得到错误到此行

 F_UserLocationByType（ '  + @UserId +'，'  + @LocationType +'）ulbt on ld.FromLocationId 

值不是正确附加，当使用函数参数传递时还有其他方法传递





我收到以下错误

消息245，级别16，状态1，过程spGetSuggestedLocationDistances，第44行
转换nvarchar值时转换失败'使用T AS 
（ 
选择ROW_NUMBER（）Over（按LocationSugDistanceId排序）作为ROWNUMBER，D。*，Count（*）over（）作为TotalRecords FROM 
 （
选择
 ld.LocationSugDistanceId，
 ld.FromLocationId，
 ulbt.Name作为FromLocation，
 tl.Name作为ToLocation，
 ld.Distance， 
 d.DivisionId，
 d.Name as Division，
 e.FirstName as SuggestedBy 
 
 
 
 LocationSuggested ld 
 --join位置fl on ld.FromLocationId = fl.LocationId 
加入位置tl on ld.ToLocationId = tl.LocationId 
 left join Division d on ld.DivisionId = d.DivisionId 
 join F_UserLocationByType （'to data type int。

解决方案

在任何TSql查询中，'+'操作数中包含的任何数字都会自动进行计算数字加法。



为了用字符串连接数字，你必须将所有数字转换成字符串类型：



< pre lang =SQL> join F_UserLocationByType（' + Cast（@UserId as nvarchar（max））+'，' ''+ @LocationType +'' '）

希望有所帮助^ _ ^



Andy

问题是+用于两个操作，加法和字符串连接，而加法优先。因此，当SQL服务器看到有一个整数时，它会尝试将其余的操作数转换为数字。您可以显式地将值转换为varchar或自己帮忙，并使用 sp_executesql [< a href =https://msdn.microsoft.com/en-us/library/ms188001.aspxtarget =_ blanktitle =New Window> ^ ]。

  SET   @ SQLQuery  = < span class =code-string>' 使用T AS 
 ... 
加入F_UserLocationByType（@ UserId，@ LocationType）ulbt on 
 ...' 
 
  EXECUTE  sp_executesql  @ SQLQuery ，N '  @ UserId int，@ LocationType int'，@ UserId = @ UserId，@ LocationType = @LocationType 

您仍然需要连接排序顺序。

Hi ,

SET @SQLQuery =              
				   'With T AS 
					 (				  
						Select  ROW_NUMBER() Over (Order By ' + @Sort + ') As ROWNUMBER,D.*,Count(*) over() as TotalRecords FROM  
						( 
						  Select   
									ld.LocationSugDistanceId,
									ld.FromLocationId,
									ulbt.Name as FromLocation,
									tl.Name as ToLocation,
									ld.Distance,
									d.DivisionId,
									d.Name as Division,
									e.FirstName as SuggestedBy
									
							
					       From      
						             LocationSuggested ld
									 --join Location fl on ld.FromLocationId = fl.LocationId
									 join location tl on ld.ToLocationId = tl.LocationId
									 left join Division d on ld.DivisionId = d.DivisionId
									 join F_UserLocationByType(' + @UserId + ',' + @LocationType + ') ulbt on ld.FromLocationId  = ulbt.LocationId
									 join Employee e on ld.CreatedBy = e.EmployeeID
						 ) D Where (1=1)'

in the above example i am getting the error to this line when i run this sp

F_UserLocationByType(' + @UserId + ',' + @LocationType + ') ulbt on ld.FromLocationId  

the values are not appending properly , is there any other way to pass when use function parameter passing


I am getting below error

Msg 245, Level 16, State 1, Procedure spGetSuggestedLocationDistances, Line 44
Conversion failed when converting the nvarchar value 'With T AS 
					 (				  
						Select  ROW_NUMBER() Over (Order By LocationSugDistanceId) As ROWNUMBER,D.*,Count(*) over() as TotalRecords FROM  
						( 
						  Select   
									ld.LocationSugDistanceId,
									ld.FromLocationId,
									ulbt.Name as FromLocation,
									tl.Name as ToLocation,
									ld.Distance,
									d.DivisionId,
									d.Name as Division,
									e.FirstName as SuggestedBy
									
							
					       From      
						             LocationSuggested ld
									 --join Location fl on ld.FromLocationId = fl.LocationId
									 join location tl on ld.ToLocationId = tl.LocationId
									 left join Division d on ld.DivisionId = d.DivisionId
									 join F_UserLocationByType(' to data type int.

解决方案

In any TSql query, any number included in a '+' operand automatically makes the calculation a numeric addition.

In order to concatenate numbers with strings you must convert all the numbers into string types:

join F_UserLocationByType(' + Cast(@UserId as nvarchar(max)) + ',''' + @LocationType + ''') 

Hope that helps ^_^

Andy

The problem is that + is used for two operations, addition and string concatenation while addition takes precedence. So when SQL server sees there is an integer it tries to convert the rest of operands to numbers too. You can cast the values to varchar explicitly or do yourself a favor and use parameters with sp_executesql[^].

SET @SQLQuery = 'With T AS
...
join F_UserLocationByType(@UserId,@LocationType) ulbt on 
...'

EXECUTE sp_executesql @SQLQuery, N'@UserId int, @LocationType int', @UserId=@UserId, @LocationType=@LocationType

You still need to concatenate the sort order though.

这篇关于如何在transct sql查询中将参数值传递给函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！