I got it figured out. In the actual stored procedure (
CREATE PROCEDURE sp_GetInformation
@UserType varchar (5),
@EntityID int
AS
DECLARE @SQL varchar (255)
Select @SQL= 'SELECT Entities.Entity AS Entity, Users.UserName, Users.FN
ame, Users.Lname, Users.Active, Users.UserType, Users.Pwd FROM Entities INN
ER JOIN Users ON Entities.EntityID = Users.EntityID'
If @UserType <> 'Admin'
Select @SQL = @SQL + ' Where Entities.EntityID = ''' + Convert(varc
har,@EntityID) + ''''
select @sql
EXECUTE (@SQL)
PRINT (@SQL)
GO
)
When in Query Analyzer, the result is a split window. I did not think anyth
ing of this until I ran the cfdump tag, then I say that the output was the
second select statement in the actual SP. So when I added the resultset att
ribute and set it to 2, I got the results that I wanted. So the problem was
in the actual SP and not in the tag. As usual, CF is doing exactly what I
told it to do.
---------- Original Message ----------------------------------
From: Craig Dudley <[EMAIL PROTECTED]>
Reply-To: [EMAIL PROTECTED]
Date: Mon, 14 Jan 2002 16:36:33 -0000
Have you tried it with returncode="No" ?
Might noit help at all, but try using cfdump for your output, should
help
debuging.
eg.
<cfdump var="#qGetInformation#">
-----Original Message-----
From: Bruce Sorge [mailto:[EMAIL PROTECTED]]
Sent: 14 January 2002 15:58
To: CF-Talk
Subject: CFStored Procedure question
OK,
I have been using SP's for quite some time with great success and I
love
them. However, I have one that is giving me some troubles. Here is the
CFStoredprocedure tag:
<cfstoredproc datasource="#DataSource#"
procedure="sp_GetInformation"
returncode="Yes">
<cfprocparam cfsqltype="CF_SQL_VARCHAR" dbvarname="@USerType"
type="In" value="#Session.UserType#">
<cfprocparam cfsqltype="CF_SQL_INTEGER" dbvarname="@EntityID"
type="In" value="#Session.EntityID#">
<cfprocresult name="qGetInformation">
</cfstoredproc>
The cfprocparam tags are sending in the correct information, and when I
run
an SQL trace I see the values are being passed. the problem is the
cfprocresult. When I do a cfoutput, I am not getting the information
passed
from the query at all. When I run the SP in the Query analyzer, all
works
well and I see all the information that I need to see. Has anyone else
seen
this issue before?
Thanks,
______________________________________________________________________
Dedicated Windows 2000 Server
PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER
Instant Activation � $99/Month � Free Setup
http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
