what is the code for create a stored procedure in this SQL CODE?
create database employee
create table emp(emp_id int,emp name string(15))
insert into emp values(1,"X")
insert into emp values(2,"Y")
I am trying to create the following stored procedure in sql server Lat and Lng are the parameters being passed from c# code behind .But I am not able to create this stored procedureit indicates with error saying undefined column name Lat,Lng
CREATE FUNCTION spherical_distance(@a float, @b float, @c float)
RETURNS float
AS
BEGIN
RETURN ( 6371 * ACOS( COS( (@a/@b) ) * COS( (Lat/@b) ) * COS( ( Lng/@b ) - (@c/@b) ) + SIN( @a/@b ) * SIN( Lat/@b ) ) )
END
sqlda.SelectCommand.CommandText = "select *, spherical_distance( Lat, 57.2958, Lng) as distance
from business
[code]...
how can I create stored procedure and write my select statement in it, I know how to create dataset then put type stored procedure and assign it to the sp ... what I want is writing my sp... how can I make it ?
check the following code, this is what I want to write put I don't know where or how !
CREATE STORED PROCEDURE SP_CATEGORY
@CATEGORY VARCHAR(30)
AS
BEGIN
SELECT LATIN_NAME, ENGLISH_NAME, ARABIC_NAME, CATEGORY
FROM FLORA, CATEGORY_LIST
WHERE FLORA.CATEGORY=CATEGORY_LIST.CATEGORY_NAME AND CATEGORY_LIST.CATEGORY_NAME IN (SELECT * FROM SPLITLIST(@CATEGORY, ','))
END
where can I write this code ?!
I have created stored procedure and student database and also asp.net application for asp.net page but it could not found stored procedure what is the mistake actually I don't no
View 7 RepliesI'm trying to create a stored procedure that get a Key and Name (both varchar), the procedure will check if a the key allready exists in the table, if it exists it will update the name, if its not exists it will add it to the table..
View 2 RepliesI want to create a dynamic stored procedure.
How can I create one?
A procedure which can be called with or without parameters passing (something like optional parameter. but don't know how to use optional parameters. plz tell me how to create a stored procedure with optional parameters, and also if it is the only way to create the dynamic stored procedures.)
[Code]....
I want create a stored procedure for insert values for Name,Company_Name,Designation,Eamil_Id,Remark then Ref_Id should automically get the value if insert in remain feilds like Name,Company_Name,Designation,Eamil_Id,Remark with out changing table structure
create procedure edifyp
(
@firstname varchar(50),
@lastname varchar(50),
@Username varchar(50),
@password varchar(50),
@cpassword varchar(50)
)
as insert into esignup
(
[firstname],
[lastname],
[username],
[password],
[cpassword]
)
values
(
@firstname ,
@lastname ,
@Username ,
@password ,
@cpassword
)
this is my sign up page store procedure, and i have created sign up page, its already runs very well,
but i am a begineer, and i am learning my self. so i need to get the user name from this table as well password to log in with these datails,
ahow can i write stored procedure for log in page, and what shuld i have to do inside the login button contol
I am trying to create a stored procedure that filters data from sql server by date but i get this error:
Conversion failed when converting datetime from character string.
my stored procedure is like:
[Code]....
the parameters are passed to the stored procedures as dateTime variables from c# code...
i would like to retrieve data from two table, how can i create a temp table and then insert data from two different table.and then use the temp table to join other table in stored procedure.if i want to select same data from two table, how can i select all distinct data from two table into temp table
View 4 Repliestrying to create a stored procedure to authenticate users, if users are authenticated their data like userid, first name, etc are returned in a cursor. However, if users are not authenticated an error is returned or something that indicates users were not authenticated.
But I don't know how to do this in a stored procedure.
If I have the inventory class and want to make it to be xml and passed to web services and then web services will call stored procedure with xml as input parameters.
how to do in coding?
Via the button1_ click event I retrieve all files in a directory (Example Follows)
[Code]....
I now need to check whether this filename exists as a field in a SQL Server table. For example, if the directory retrieves a file named ExampleFile.txt which does not exist in the (Table = TableFileStore;s Field = FileRetrievals) then I want to insert the filename.
How do I construct a stored procedure to pass the filename(s) into a stored procedure as a parameter? Do I need to also create an array of filenames and pass the array into my stored procedure? Does the stored procedure belong within the foreach loop?
Using vs.net 2005 and sql server 2005.
Hope this makes sense: I need to create a stored procedure that I am going to use as a datasource for a crystal report in vs.net.
The issue is that the data is stored in 2 different kinds of databases, one is sql server and the other is plb files.
I have an odbc connections created for each separately however I need to make one query that joins data fields from both the sql server db tables and the plb db, how do I do this in one query that I will use in a stored procedure?
I need to search my sql database using full text search.
My question is: How do I create the stored procedure and query?
The full text is already created and I ma using asp.net and c# in my application?
Do I need to include the name of the full text index in the stored procedure and query?
I could probably figure this out if I tried to, but I have been working so long on code, I'm a little fried
I have a stored procedure, and I want to execute another stored procedure during a time period of lets say 1/1/2011 to 12/31/2011
How Would I accomplish this?
This is surareddy. i nead some small clarification in the "Stored Procedure"
how to convert the oracle Stored Procedure to sqlserver2005/2008 Stored Procedure.
right now i am enhancing the project that project already developed the oracle Stored Procedure. now our company is using sqlserver 2005/2008.
how to convert the Oracle Stored Procedure to sqlserver 2005 Stored Procedure
Initially, I have tried to use stored procedure. But I changed my mind and preferred to call sql query in codebase with command text. However, it stills tries to find initially-called stored procedure (which is neither called or exists).I think that it is related caching. But I tried it with different browsers it did not work.What might be the reason?
View 4 RepliesI need to create stored procedure to use it for multi value parameter.. I createdd new data set and changed the type to stored procedure but how can I write my select statement in it?
View 4 RepliesI'm trying to create a stored procedure that does two things:1st - Insert a new record in the contactus table.2nd - Gets the last primary key value from table contactus.I've created the following Stored Procedure but this is giving me an error when being executed.
[Code]....
I have created a stored procedure shown below ,how will i call this from c# code behind to get the result and results are stored in dataset.
USE [Test]
GO
/****** Object: StoredProcedure [dbo].[tesproc] Script Date: 09/01/2010 13:00:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[tesproc]
-- Add the parameters for the stored procedure here
@a float, @b float, @c float,@d int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
select Id, Name1,ZipCode,StreetName,StreetNumber,State1,Lat,Lng, ( 6371 * ACOS( COS( (@a/@b) ) * COS( (Lat/@b) ) * COS( ( Lng/@b ) - (@c/@b) ) + SIN( @a/@b ) * SIN( Lat/@b ) ) ) as distance from business_details where ( 6371 * ACOS( COS( (@a/@b) ) * COS( (Lat/@b) ) * COS( ( Lng/@b ) - (@c/@b) ) + SIN( @a/@b ) * SIN( Lat/@b ) ) )<@d
END
If i execute this stored procedure in sql server its working fine with this following call
exec dbo.tesproc 12.9216667 ,57.2958,77.591667,1
So I am getting this error message:
Cannot implicitly convert type 'System.Data.SqlClient.SqlParameter' to 'string' I have a stored procedure on SQL 2000 returning an email from a database table (aspnet_Membership). The email type from the table is nvarchar. I have a method (in C# in the code behind) that gets that value. But the debugger doesn't like the last line:
return returnEmail; // returnEmail is a string variable we created to capture the stored procedure's return value
I've tried doing returnEmail.toString() in several places, but still get the same error message for the last line of code (returning the email).
This is the stored procedure:
I have the following stored procedure:
ALTER PROCEDURE Pro_members_Insert
@id int outPut,
@LoginName nvarchar(50),
@Password nvarchar(15),
@FirstName nvarchar(100),
@LastName nvarchar(100),
@signupDate smalldatetime,
@Company nvarchar(100),
@Phone nvarchar(50),
@Email nvarchar(150),
@Address nvarchar(255),
@PostalCode nvarchar(10),
@State_Province nvarchar(100),
@City nvarchar(50),
@countryCode nvarchar(4),
@active bit,
@activationCode nvarchar(50)
AS
declare @usName as varchar(50)
set @usName=''
select @usName=isnull(LoginName,'') from members where LoginName=@LoginName
if @usName <> ''
begin
set @ID=-3
RAISERROR('User Already exist.', 16, 1)
return
end
set @usName=''
select @usName=isnull(email,'') from members where Email=@Email
if @usName <> ''
begin
set @ID=-4
RAISERROR('Email Already exist.', 16, 1)
return
end
declare @MemID as int
select @memID=isnull(max(ID),0)+1 from members
INSERT INTO members (................................
When I run this page, signup.aspx, provide required fields and click submit, the page simply reloads and the database table does not reflect the newly-inserted record. How do I catch the error messages that might be returned from the sproc? how to change signup.aspx so that the insert occurs.
I am trying to create dataset (.xsd) and link it with stored procedure in MySQL database. the problem is when i connect it it's changing the schema name. like if I have this stored procedure name or table name or whatever :
News.tbNews the builder it self change it to :
def.News.tbNews. MySql version 5.5.8, MySqlConnector 6.3.4 I searched for a solution for this problem and one of the answers was to change the MySqlConnector from 6.3.4 to 6.3.6 !!! I download the new connector 6.3.6 but it's giving me Error while installing it.
i am creating new database in my sql server 2000.But this database donot have any system storeprocedure. I am not able to create new store procedure in this database.
View 2 Replies