EasyCFM.COM ColdFusion Forums / MS SQL Server Related Issues / Question about stored procedure

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: Question about stored procedure -- page: 1 2

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts last

kicker
01-01-2007 @ 11:45 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 717
Joined: Oct 2004

Ok this may seem like a silly question, but...
I am trying to write a stored procedure in a MS SQL Server 2005 db. Here is an example of my code:

<!---Create the store procedure--->
<cfquery datasource="mydb">
    CREATE PROCEDURE sp_Search (@qry CHAR) as
     SELECT Name,DOB,Tile,ID FROM Users_tbl
     WHERE Name = '@qry'
</cfquery>
<!---Call the procedure I just created--->
<cfstoredproc datasource="mydb" procedure="sp_Search" returncode="yes">
     <cfprocparam dbvarname="@qry" cfsqltype="cf_sql_char" value="John" type="in">
     <cfprocresult name="Search">
</cfstoredproc>
<!---Output the results--->
<cfdump var="#Search#">


Now the problem is it creates the procedure fine, it calls it fine (I know because I have output the cfstoredproc.StatusCode and it says 0 Which means it executed fine.)But I get no results when I know for a fact that it should return at least one result because when I do a regular query it comes back with one result. So I think it has something to do with the syntax in the stored procedure needing to be different but I don't know how to do it. Also it does return an empty query result when I do the dump.

Any help would be a life saver cause I am at my wits end with this. Thanks.


Craig

This message was edited by kicker on 1-2-07 @ 10:06 AM

kicker
01-02-2007 @ 5:12 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 717
Joined: Oct 2004

Does anyone have any idea why this isn't working. It works fine with numbers, but not with words.

Craig

Scarecrow
01-02-2007 @ 5:15 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Junior Member
Posts: 109
Joined: Oct 2004

I think your problem may relate to the type def of CHAR.

If you have a column (lets say of 10 in length), then if the value in @qry is 5 in length, then 5 blank spaces will be added to the value.
So, unless the value is always going to be a certain number of characters always use varchar.

Also do not enclose @qry in single quotes

WHERE Name = @qry

I would also suggest not using reserved words for your column names (ID, Name, Title, ect)

Ken

www.krcaldwell.com

kicker
01-02-2007 @ 5:37 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 717
Joined: Oct 2004

I switched the char to varchar in the database and in the stored procedure. I then tried it again and it failed again to produce any results. Instead I get the same blank query structure. I also removed and tried with the quotes gone. Any other suggestions.
Question

Craig

PS I don't actually have my column names as those shown above they are different. I do not want the column names shown on here for db security. But I assure you that they are NOT reserved words.

This message was edited by kicker on 1-2-07 @ 5:41 PM

Scarecrow
01-02-2007 @ 5:57 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Junior Member
Posts: 109
Joined: Oct 2004

I don't use 2005 as yet, but it is the same as 2000.

I would suggest using query analyser to debug.

exec sp_Search 'John'

You may then get some info on whats happening.
Ensure that the query actually returns at least one record.

Then use this query in the sp (use the hard coded value in the where clause)

Once this is working then use the paramater.

Ken

www.krcaldwell.com

kicker
01-02-2007 @ 6:14 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 717
Joined: Oct 2004

Thanks for your help but that did not give me any errors to work from it returned a blank data set. I do believe that there have to be some differences to the 2000 version as to the 2005 version or what would be the sense.

Craig

Scarecrow
01-02-2007 @ 6:17 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Junior Member
Posts: 109
Joined: Oct 2004

Does the query return the correct results ?

If so, copy the entire query into the sp and test the sp.

If the sp worked correctly, change the hard coded value with the paramater value.

Ken

www.krcaldwell.com

kicker
01-02-2007 @ 6:29 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 717
Joined: Oct 2004

The query returns a empty array each way I have tried above when I do it through a regular <cfquery> with the sql statement it works fine and returns results.

Craig

Scarecrow
01-02-2007 @ 9:16 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Junior Member
Posts: 109
Joined: Oct 2004

If the query does not work through CF, then I would suggest the problem is with the driver being used or the dsn connection to the db.

As this is 2005, there may be some problems that I'm not aware off.

You may need to do a google to find the solution.

Can you use cfquery to select anything from this db ?

Ken

www.krcaldwell.com

kicker
01-02-2007 @ 10:15 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 717
Joined: Oct 2004

As I have said it works through a cfquery tag just fine. With hard coding the search criteria and sending a variable. The information comes up and it is displayed with results that are expected. NO empty query results. IT SHOWS the information as it should when using a cfquery with a SQL statement.  

Craig

PAGE: 1 2

Website Designed and Developed by Pablo Varando.