Almost embarrassed to ask - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Almost embarrassed to ask

Author

Topic

John T.
Posting Yak Master

112 Posts

Posted - 2003-04-28 : 15:06:50

I have been messing with this for quite sometime. I am calling a stored procedure that is far simpler than some I have been doing lately. But I know it is not working.
CREATE PROCEDURE LoginProc @varInt VarChar(50),@cname VarChar(50) OUTPUT AS
SELECT @cappername = Cap FROM Utable WHERE UserName = @varInt
I have tried a set also.
SELECT Cap FROM Utable WHERE UserName = @varInt
SET @cname = Cap

I have a table with three fields. I am simply trying to match the username with the input value. And return the appropriate other field. I get no error messages. But when I insert checks to look and see if the value is being returned, it is not. Am I missing something with this code?
Thanks. And hope you all had a good weekend.
John

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-04-28 : 15:16:49

This Is what I See.

Must declare the variable '@cappername'

Jim

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-28 : 15:17:58

Since you're naming your input @varInt, I'm assuming that UserName is defined in the table as Integer?

All I can say is that datatypes have to match.

What is the DDL for your table?

PS Error checking is a good idea btw

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-28 : 15:19:02

CREATE PROCEDURE LoginProc
(@varInt VarChar(50), @cname VarChar(50) OUTPUT)

AS

SET NOCOUNT ON

SELECT @cname = Cap
FROM Utable
WHERE UserName = @varInt

RETURN

If this doesn't work, then what does this return (modify @varInt variable):
DECLARE @cname VARCHAR(50)
DECLARE @varInt VARCHAR(50)

SET @varInt = <whatever value @varint is>

SELECT @cname = Cap
FROM UTable
WHERE Username = @varInt

PRINT @cname

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-28 : 15:22:36

Good point Jim, that code wouldn't even compile.

Also you say you're not seeing any errors? How are you calling the sproc. (besides that how did you complie it).

How do you test it? I would reccomend calling it from QA

DECLARE @X

EXEC LoginProc '1', @x

SELECT @X

Brett

8-)

John T.
Posting Yak Master

112 Posts

Posted - 2003-04-28 : 15:31:00

My bad. cname, not cappername. And yes, varInt is bad representation, although it is a string.
The problem is not with the sproc. I am sure it is with my vb.net. Sorry.

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-28 : 15:32:44

Hey John, you're a veteran already

Brett

8-)

John T.
Posting Yak Master

112 Posts

Posted - 2003-04-28 : 18:11:07

If any of you know anything about vb.net, you may be able to point something out to me. I have used this type of code quite a few times for stored procedures. I can't understand why it just doesn't seem to call the procedure. I can go into the procedure itself and make the query simple enough that it has to return a value. Now in the past, I have never passed anything except an integer out of my sproc. So could that be my problem? I am trying to pass a character string out. Is there anyway to tell if the sproc is being called? Remember, I am a rookie. I'll be a rookie the day I die.
<code>
Dim varStr = txtUserName.Text
Dim cname As String
Dim connString As String
Dim sqlConnection As sqlConnection = New sqlConnection(Configuration.AppSettings("connString"))

sqlConnection.Open()

Dim cmd As New sqlCommand("LogProc", sqlConnection)
cmd.commandType = commandType.StoredProcedure

Dim param1 As New sqlParameter("@varStr", sqlDBType.varchar,50)
param1.Direction = ParameterDirection.Input
param1.value = varStr
cmd.Parameters.Add(param1)

Dim param2 As New sqlParameter("@cname", sqlDBType.varchar,50)
param2.Direction = ParameterDirection.Output
param2.value = cname
cmd.Parameters.Add(param2)

cmd.ExecuteNonQuery()
sqlConnection.Close()
</code>
Now somebody pointed out to me that the "cmd.ExecuteNonQuery" command may not allow a return value. In the other sprocs that I have used, the integer values were no problem passing.
Been looking at this since I went to bed last night. I know these computers only do what you tell them to do. Not always what you want them to do.
Thanks.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-28 : 18:27:39

In the sp you have given the parameter is named @varint whereas you are passing @varstr. Unfortunately .net binds the parameter names which I think is a really bad idea.

You should be getting an error if this is the case.

I seem to have given all my .net code away with a laptop

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2003-04-28 : 18:32:31

Don't assign a value to the output parameter (you are assigning it an empty object reference cname). Access it's value after you execute the procedure

 
.........
cmd.ExecuteNonQuery() 
sqlConnection.Close() 

cname = param2.value

HTH
Jasper Smith

0x73656c6563742027546f6f206d7563682074696d65206f6e20796f75722068616e6473203f27

John T.
Posting Yak Master

112 Posts

Posted - 2003-04-28 : 19:31:26

jasper-
I figured it out. If I reference the param1 and param2 values, I have it.
Thanks for the help.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-04-29 : 02:05:26

quote:

Unfortunately .net binds the parameter names which I think is a really bad idea.

Why is it a bad idea ? I think it is the way they should have done it in the first place.

Yes it takes a bit of a rethink to do certain things sometimes, but that is only because we have had to come up with hacks to get around the fact that ADO didn't know what parameter it had, despite the fact they were named.

Damian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources