Stored Procedures retrieving values
-
I am new to stored procedures. I want to know how to retrieve string values from stored procedures. Like i created a website in which when user clicks a particular artists image then it's file location is picked up from sql server. But im not getting way to retrieve from there through procedures. -----stored procedure------- create procedure arts (@Artist nchar(50)) AS return (SELECT ret = Location from Artist_loc where Artist = @Artist ) in aspx.cs page----------- string Artist = Request.QueryString["art"]; SqlConnection cn = new SqlConnection(@"Data Source=BLACK_RHYMES-PC\MSSMLBIZ;Initial Catalog=Muzik;Integrated Security=True"); SqlCommand cmd = new SqlCommand(); cmd.CommandText = "dbo.arts"; cmd.Parameters.Add("@Artist",SqlDbType.NChar,50).Value = Artist; cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = cn; i got struck after this what lines should i add to retrieve results..... *THNX*
-
I am new to stored procedures. I want to know how to retrieve string values from stored procedures. Like i created a website in which when user clicks a particular artists image then it's file location is picked up from sql server. But im not getting way to retrieve from there through procedures. -----stored procedure------- create procedure arts (@Artist nchar(50)) AS return (SELECT ret = Location from Artist_loc where Artist = @Artist ) in aspx.cs page----------- string Artist = Request.QueryString["art"]; SqlConnection cn = new SqlConnection(@"Data Source=BLACK_RHYMES-PC\MSSMLBIZ;Initial Catalog=Muzik;Integrated Security=True"); SqlCommand cmd = new SqlCommand(); cmd.CommandText = "dbo.arts"; cmd.Parameters.Add("@Artist",SqlDbType.NChar,50).Value = Artist; cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = cn; i got struck after this what lines should i add to retrieve results..... *THNX*
Use Output parameter
Create procedure arts
@Artist nchar(50),
@Ret NVARCHAR(40) OUTPUT
BEGIN
...
...
SET @Ret = yourresult...
ENDFrom code write
SqlParameter oparam = new SqlParameter("@Ret", SqlDbType.NVarchar, 50);
oparam.Direction=ParameterDirection.Output;
cmd.Parameters.Add(oparam);
cmd.ExecuteNonQuery();Now you will find the return statement in
oparam.value. . :rose:Abhishek Sur
My Latest Articles **Create CLR objects in SQL Server 2005 C# Uncommon Keywords Read/Write Excel using OleDB
**Don't forget to click "Good Answer" if you like to.
-
Use Output parameter
Create procedure arts
@Artist nchar(50),
@Ret NVARCHAR(40) OUTPUT
BEGIN
...
...
SET @Ret = yourresult...
ENDFrom code write
SqlParameter oparam = new SqlParameter("@Ret", SqlDbType.NVarchar, 50);
oparam.Direction=ParameterDirection.Output;
cmd.Parameters.Add(oparam);
cmd.ExecuteNonQuery();Now you will find the return statement in
oparam.value. . :rose:Abhishek Sur
My Latest Articles **Create CLR objects in SQL Server 2005 C# Uncommon Keywords Read/Write Excel using OleDB
**Don't forget to click "Good Answer" if you like to.
Create procedure arts @Artist nchar(50), @Ret NVARCHAR(40) OUTPUT begin set @ret = select Location from Artist_loc where Artist = @Artist end it's giving error incorrect syntax near keyword 'select'
-
Create procedure arts @Artist nchar(50), @Ret NVARCHAR(40) OUTPUT begin set @ret = select Location from Artist_loc where Artist = @Artist end it's giving error incorrect syntax near keyword 'select'
greendragons wrote:
set @ret = select Location from Artist_loc where Artist = @Artist
It should be
Select TOP 1 @ret = Location from Artist_loc where Artist= @Artist
SET will only be used when you initialize variable like
SET @x = 10
Hope you got the difference between the two. :thumbsup:
Abhishek Sur
My Latest Articles **Create CLR objects in SQL Server 2005 C# Uncommon Keywords Read/Write Excel using OleDB
**Don't forget to click "Good Answer" if you like to.
-
greendragons wrote:
set @ret = select Location from Artist_loc where Artist = @Artist
It should be
Select TOP 1 @ret = Location from Artist_loc where Artist= @Artist
SET will only be used when you initialize variable like
SET @x = 10
Hope you got the difference between the two. :thumbsup:
Abhishek Sur
My Latest Articles **Create CLR objects in SQL Server 2005 C# Uncommon Keywords Read/Write Excel using OleDB
**Don't forget to click "Good Answer" if you like to.
Sry...still giving incorrect syntax near keword select :(
-
Sry...still giving incorrect syntax near keword select :(
The code he gave you is correct. I suggest you buy a book on SQL, and ask SQL questions in the SQL forums.
Christian Graus Driven to the arms of OSX by Vista. Read my blog to find out how I've worked around bugs in Microsoft tools and frameworks.
-
Sry...still giving incorrect syntax near keword select :(
have you done like this ?
Create procedure arts
@Artist nchar(50),
@Ret NVARCHAR(40) OUTPUT
AS
begin
Select TOP 1 @ret = Location from Artist_loc
where Artist = @Artist
end:confused:
Abhishek Sur
My Latest Articles **Create CLR objects in SQL Server 2005 C# Uncommon Keywords Read/Write Excel using OleDB
**Don't forget to click "Good Answer" if you like to.