can i execute a dynamic SQL in IF EXISTS
-
I am trying to execute a dynamic SQL as part IF EXISTS statement. I get syntax error problems.
Here is the sample code,
Declare @DynamicSQl varchar(250), @UserGroupId Int
Select @DynamicSQl = 'select username from userinfo where usergroupid = '+ @UserGroupId
If exists (exec(@DynamicSQl)
begin
do something
end
Else
begin
do something else
end
-
-
Syntax, my friend, syntax:
You cant concat a varchar and an int (@UserGroupId Int) .. this is not Java
Also, remember to complete the if statement
If exists ( exec(@DynamicSQl) )
begin...
-
Sorry you cannot use EXEC(dynamic) in that fashion. However, all you want to know is if records exist so this can help you get.
Declare @DynamicSQl varchar(250), @UserGroupId Int
SET @DynamicSQl = 'select @retVal = COUNT(username) from userinfo where usergroupid = '+ CAST(@UserGroupId AS VARCHAR(20))
DECLARE @retVal int
EXEC sp_executesql @DynamicSQl, '@retVal int output', @retVal output
IF @retVal > 0
BEGIN
...........
That should get you around the problem.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
-
thanks for the alternate solution it works.
I had also worked around the problem with a similar solution. Our company does not let us use "sp_executesql" for security reasons.
-
Instead of doing a count of records, just return 1 if what you want exists
Declare @DynamicSQl nvarchar(250),
@UserGroupId Int,
@x nvarchar(100)
select @x = '@retVal int output'
SET @DynamicSQl = 'select @retVal = 1 from users where chusername = ''aa one'''
DECLARE @retVal int
EXEC sp_executesql @DynamicSQl, @x, @retVal output
select @retval
will return a null if no records exist, otherwise a 1. gets rid of the counting of records
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply