DECLARE @pddatetime smalldatetime,
			@sqlStatement varchar(2000) --Dynamic Query
	SET @pddatetime=GETDATE()

	   ---- this query will return the user and their user group
	   SELECT @sqlStatement = 'select  distinct t1.loginid, 
			t1.username, t1.userdescription, t4.groupname,t4.groupdescription
			from hsuser t1 with (nolock) 
	         join hsu2uglink t2 with (nolock)  
				on t1.objectid = t2.user_oid
	         join huserassignment t3 with (nolock) 
				on t2.group_oid = t3.usergroup_oid
	         join hsusergroup t4 with (nolock) 
				on t2.group_oid=t4.objectid
 	         where t1.endtime >= ' + @pddatetime
	         ' and t1.begintime <= ' + @pddatetime +
	         ' and t3.endtime >= ' + @pddatetime +
	         ' and t3.begintime <= ' +@pddatetime +
	         ' and t4.endtime >= ' +@pddatetime +
	         ' and t4.begintime <= ' +@pddatetime +
		' order by groupname'

PRINT(@sqlStatement)

quote:

---

Originally posted by dmorand

thanks for the help! I setup a casted variable ahead of time and used that rather than keep having to cast the value over and over in the dynamic query.

I have it setup as a dynamic query right now because I'm going to be adding to the query, I just wanted to get the base version of it working first.

---

ALTER    PROCEDURE [HA_Security]
	@report_type varchar(50), --User,Usergroup,Role
	@user_list varchar(500), --List of Users
	@usergroup_list varchar(500), --List of Usergroups
	@role_list	varchar(500) --List of Roles
AS
BEGIN
	DECLARE @pddatetime smalldatetime,
			@convdatetime varchar(50),
			@sqlStatement varchar(2000) --Dynamic Query
	SET @pddatetime = GETDATE()
	SET @convdatetime = convert(varchar(50),@pddatetime,101)

	PRINT(@pddatetime)
	PRINT(@convdatetime)
	IF @report_type = 'UserGroup'
	BEGIN	
		PRINT(@report_type)
	   ---- this query will return the user and their user group
	   SELECT @sqlStatement = 'select  distinct t1.loginid, 
			t1.username, t1.userdescription, t4.groupname,t4.groupdescription
			from hsuser t1 with (nolock) 
	         join hsu2uglink t2 with (nolock)  
				on t1.objectid = t2.user_oid
	         join huserassignment t3 with (nolock) 
				on t2.group_oid = t3.usergroup_oid
	         join hsusergroup t4 with (nolock) 
				on t2.group_oid=t4.objectid
 	         where t1.endtime >= ' + @convdatetime
	         ' and t1.begintime <= ' + @convdatetime +
	         ' and t3.endtime >= ' + @convdatetime +
	         ' and t3.begintime <= ' @convdatetime +
	         ' and t4.endtime >= ' + @convdatetime +
	         ' and t4.begintime <= ' + @convdatetime
		IF Len(@usergroup_list) > 0 
		BEGIN
			SELECT @sqlStatement = @sqlStatement + 
				' and loginid IN(' + @user_list + ')'
		END
		SELECT @sqlStatement = @sqlStatement + 
		' order by groupname'
PRINT(@sqlStatement)

ALTER    PROCEDURE [HA_Security]
	@report_type varchar(50), --User,Usergroup,Role
	@user_list varchar(500), --List of Users
	@usergroup_list varchar(500), --List of Usergroups
	@role_list	varchar(500) --List of Roles
AS
BEGIN
	DECLARE @pddatetime smalldatetime,
			@convdatetime varchar(50),
			@sqlStatement varchar(2000) --Dynamic Query
	SET @pddatetime = GETDATE()
	SET @convdatetime = convert(varchar(50),@pddatetime,101)

	
	IF @report_type = 'UserGroup'
	BEGIN	
		
	   ---- this query will return the user and their user group
	   select  distinct t1.loginid, 
			t1.username, t1.userdescription, t4.groupname,t4.groupdescription
			from hsuser t1 with (nolock) 
	         join hsu2uglink t2 with (nolock)  
				on t1.objectid = t2.user_oid
	         join huserassignment t3 with (nolock) 
				on t2.group_oid = t3.usergroup_oid
	         join hsusergroup t4 with (nolock) 
				on t2.group_oid=t4.objectid
 	         where t1.endtime >=  @convdatetime
	          and t1.begintime <=  @convdatetime 
	          and t3.endtime >=  @convdatetime 
	          and t3.begintime <=  @convdatetime 
	          and t4.endtime >=  @convdatetime 
	          and t4.begintime <=   @convdatetime
		and (Len(@usergroup_list) = 0 
		or ',' + @user_list + ',' LIKE '%,'+cast(loginid as varchar(20))+ ',%'
)
order by groupname
END

quote:

---

Originally posted by dmorand

Also, why couldn't I use the IF statement? I am a programmer so I'm used to using IF statements and loops. I have to say that SQL programming has been the hardest conceptually to understand.

---

quote:

---

Originally posted by dmorand

When would it be ideal to use dynamic sql? I'm trying to think of a situation where I'd use it.

---

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by dmorand

When would it be ideal to use dynamic sql? I'm trying to think of a situation where I'd use it.

---

consider scenario where you objects might change dynamically or when you cant determine the columns required in resultset, like in case of cross tab. here you can use dynamic sql

---