top problem - 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 2005 Forums

Transact-SQL (2005)

top problem

Author

Topic

Todd2006
Starting Member

18 Posts

Posted - 2009-02-12 : 11:04:16

I have my stored procedure like this
[CODE]
Create PROCEDURE [dbo].[Update_Status]
@id int,
@status varchar(1000),
@comments varchar(8000)

As
Update dbo.Status
set Status = @status, Comments = @comments where
Id=@id

Select @outputpassword = TOP 1* FROM Password
[/CODE]

it gives an error saying Incorrect syntax near top

any idea
Edit/Delete Message

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-12 : 11:06:20

Select TOP 1 @outputpassword = Col1 FROM Password

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-12 : 11:06:45

[code]
Create PROCEDURE [dbo].[Update_Status]
@id int,
@status varchar(1000),
@comments varchar(8000)

As
Update dbo.Status
set Status = @status, Comments = @comments where
Id=@id

Select TOP 1 @outputpassword = column name here FROM Password
[/code]

1.use actual column name there
2.also use TOP 1 before variable

Todd2006
Starting Member

18 Posts

Posted - 2009-02-12 : 11:21:20

it gives an error saying outputpassword was not declared

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-12 : 11:27:44

yup. you need to declare it too.


Create PROCEDURE [dbo].[Update_Status]
  @id int,
  @status varchar(1000),
  @comments varchar(8000)
  
As
declare @outputpassword yourdatatype here 
Update dbo.Status 
set Status = @status, Comments = @comments where
Id=@id

Select TOP 1 @outputpassword = column name here FROM Password

Todd2006
Starting Member

18 Posts

Posted - 2009-02-12 : 11:31:40

Ok now this is my code

[CODE]
Set adocmd = Server.CreateObject("ADODB.Command")
adocmd.CommandText = "dbo.Update_Status"
adocmd.ActiveConnection = Conn
adocmd.CommandType = 4

adocmd.Parameters.Append adocmd.CreateParameter("outputpassword", 200, 4, 100)
adocmd.Parameters.Append adocmd.CreateParameter("id", 3, 1, 16, idnumber)
adocmd.Parameters.Append adocmd.CreateParameter("status", 200, 1, 1000, status)
adocmd.Parameters.Append adocmd.CreateParameter("comments", 200, 1, 1000, comments)
adocmd.Execute

getnewpass = adocmd.Parameters("outputpassword").Value
Response.write "getnewpass :-" & getnewpass & "<br/>"

[/CODE]

it doesnt display the password

[CODE]
Create PROCEDURE [dbo].[Update_Status]
@id int,
@status varchar(1000),
@comments varchar(8000)

As

declare @outputpassword varchar(100)
Update dbo.Status
set Status = @status, Comments = @comments where
Id=@id

Select TOP 1 @outputpassword = Pass FROM Pasword
[/CODE]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-12 : 11:34:59

[code]Create PROCEDURE [dbo].[Update_Status]
(
@outputpassword varchar(100) OUTPUT
@id int,
@status varchar(1000),
@comments varchar(8000)
)
As

SET NOCOUNT ON

Update dbo.Status
set Status = @status,
Comments = @comments
where Id=@id

Select @outputpassword = Pass
FROM Pasword
--WHERE SomeCol = 'SomeValue'[/code]I hope you realize you must have a WHERE clause for the Password retreival thingy...

E 12°55'05.63"
N 56°04'39.26"

Todd2006
Starting Member

18 Posts

Posted - 2009-02-12 : 11:52:36

i tried it same error

Procedure or Function 'Update_Status' expects parameter '@outputpassword', which was not supplied.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-12 : 12:38:14

Is 4 the correct type?
isn't that return_value, rather than inputoutput parameter type?

E 12°55'05.63"
N 56°04'39.26"

Todd2006
Starting Member

18 Posts

Posted - 2009-02-12 : 12:40:42

I have this line

adocmd.Parameters.Append adocmd.CreateParameter("outputpassword", 200, 4, 100)

the 200 means its varchar
4 means return value
100 means the size

so when you see i declared varchar(100) in the stored procedure thats the size which is same as i declared in asp page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-12 : 12:47:11

A return value must be INT, and cannot be VARCHAR!
Change parameter type to INPUT/OUTPUT type (which I believe is #3).
And use my last suggestion.

E 12°55'05.63"
N 56°04'39.26"

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-02-12 : 12:50:55

PRoblem is not with SQL

You need to create an output parameter explicitly in your
Dim clsParm As New ADODB.Parameter

With cmd
Set clsParm = .CreateParameter("@outputpassword", adVarChar, adParamOutput,1200)
.Parameters.Append clsParm
.Execute Options:=adExecuteNoRecords
End With

getnewpass = clsParm.Value
Response.write "getnewpass :-" & getnewpass & "<br/>"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-12 : 12:54:28

A return value must be INT, and cannot be VARCHAR.

E 12°55'05.63"
N 56°04'39.26"

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-02-12 : 13:04:32

Peso what do you mean? an OUTPUT value from a stored procedure cannot be VARCHAR? I don't understand what you mean.

text, ntext, and image parameters cannot be used as OUTPUT parameters, unless the procedure is a CLR procedure.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-12 : 13:21:47

I say a return value cannot be varchar, only int.
You surely must know the difference between output parameter and a stored procedure return value?

E 12°55'05.63"
N 56°04'39.26"

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-02-12 : 13:30:31

My apologies Patron Saint Peso, I did not read your post carefully.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-12 : 20:41:52

quote:
Originally posted by Todd2006

i tried it same error

Procedure or Function 'Update_Status' expects parameter '@outputpassword', which was not supplied.

when you call the procedure are you passing any value for @outputpassword parameter? i think you're not which is why it errors.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-13 : 02:56:32

He didn't pay attention to the rewritten procedure head I made.

E 12°55'05.63"
N 56°04'39.26"

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-02-13 : 10:40:54

you can't do this

adocmd.Parameters.Append adocmd.CreateParameter("outputpassword", 200, 4, 100)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources