I've created a SQL stored proc and want to change it to use a local variable to reduce process time..
Major lines of my SP:
[Code]....
I've been trying to use a variable for AVG(AA.Count) so that I don't have to process it three times,
[Code]....
=> this stored proc does not work, and I am not too sure where I should declare my local variable to use it in SELECT statement..
Stored procedure ALTER proc [dbo].[spSearchCombo](@searchBy nvarchar(50),@searchKey nvarchar(50)) as select * from CD_DETAILS where @searchBy like '%' +@searchKey+ '%' I'm using ASP.net2.0 with c# to extract rows basedon search key from a text box and searchBy for the column to be searched. When i use column name instead of @searchBy which comes from value selected from a ddropdownlist i get the desired result . There seems to be a problem with format of @searchBy and i get a blank page.
protected void btnSearch_Click(object sender, EventArgs e)
{
string constring = ConfigurationManager.AppSettings.Get("con").ToString();
SqlConnection conn = new SqlConnection(constring);
conn.Open();
SqlCommand cmdSP = new SqlCommand("spSearchCombo", conn);
cmdSP.CommandType=CommandType.StoredProcedure;
cmdSP.Parameters.Add(new SqlParameter("@searchBy",SqlDbType.NVarChar,50));
cmdSP.Parameters["@searchBy"].Value=ddlSearchBy.SelectedValue.ToString();
cmdSP.Parameters.Add(new SqlParameter("@searchKey", SqlDbType.NVarChar, 50));
cmdSP.Parameters["@searchKey"].Value=txtSearch.Text.Trim();
SqlDataAdapter da=new SqlDataAdapter(cmdSP);
DataSet ds=new DataSet();
da.Fill(ds);
this.dgv1.DataSource=ds.Tables[0].DefaultView;
dgv1.DataBind();
}
I've written a stored procedure which uses a table variable. The stored procedure is shown below:
[Code]....
The table variable @CaseNumbers is declared, and is a bunch of values are inserted using a table-valued function called SplitStringIntoInts. At this point I can select from @CaseNumbers and this works fine.But when I try to join onto @CaseNumbers, the name '@CaseNumbers' in the ON clause is highlighted with the error 'Must declare scalar variable @CaseNumbers'. What?! It's not scalar, I declared it ages ago and I have used it since!
Is it possible to save execute stored proc command in a string variable and then execute string variable?
Does this logic fits into LINQ2SQL? Why I want to do this - INstead of passing user control references to server, I want to save user control values to string in client side java script and then pass string to server to do actual stored proc execution. Hopefully this will improve performance.
I have this SPDECLARE @PERC VARCHAR(50)
DECLARE @nome_soc_repl VARCHAR(50)
set @PERC = '(select round((' + @nome_soc_repl + ' /(select sum ('+ @nome_soc_repl+ ') from exptot_perc))*100,2)
from exptot_perc)'
EXEC (@PERC)
nome_soc_repl is a varible that contains my table name
so I need to extrac the result of EXEC...for examples 15...... and put it on another variable.....
Here's a stored procedure I created that uses another stored procedure inside it to return a value:
[Code]....
Note the 'exec dirinfo.dbo.d_searchempbyname @TicketSubName' part. That stored procedure will return a single record. What I need is to get a single column from that recordset (in this case, the email address of the employee), and store that value in a variable.
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 RepliesDoes anybody if it is possible that a stored procedure returns rows which is the result of the execution of another sp? Something like..
[Code]....
i want to return output parameter from 1 storeprocedure. into another stored procedure.
View 7 RepliesI want to apply other stored procedure select query on result of first stored procedure.
View 1 RepliesI am using Visual Studio 2008 and SQL Server 2008 and have added a SQL datasource to my form.
When I configure the datasource, I choose my connection string which looks like this in my webconfig:
<connectionStrings>
<add
name="PSFSPRD_ConnectionString"
connectionString="Data
The database I am using has a schema called EQB and as such, my stored procedures are named as EQB.usp_SelectFunds, EQB.usp_SelectAccount, etc.
On the select tab of the Configure Data Source screen, I choose to use a stored procedure. The dropdown shows my stored procedures, however, the schema name does not show up in front of the stored procedures in the drop down. I see only usp_SelectFunds, usp_SelectAccount, etc.
I select one of the stored procedures and when I click TEST, I get the message that the stored procedure is not found. If I instead choose to use a SQL statement instead on the configuration screen and enter EXEC EQB.usp_SelectFunds and click TEST, it works fine.
Why are my stored procedures not showing up correctly in the stored procedure drop down and how can I fix this?
I am calling a stored procedure, that exists in a SQL Server 2005 database, using ADO.NET, in which I instantiate a sql command object.
The stored procedure has an ouput variable like '@packageCode VARCHAR(100) = NULL OUTPUT'.
Can I set it's value when creating the sql command object? Or I can never set a value for an output type variable in a stored procedure?
I have this procedure
[Code]....
I don't know what variable should be put in where clause for tblVisitor.visitorID because there's a variable that was already passed by an output identity parameter by a different stored procedure. What will I put in the where clause for visitor id?
how to use stored procedure in asp.net for for manipulation form sql server (in details).
View 2 Repliesparameters inside the stored procedure.How to write .
View 6 RepliesI want to send an image to a stored procedure variable that is varbinary(max) field, It works correctly when an image selected by fileupload and my image property has image file, and it can send to stored procedure. but when fileupload is empty I want to send NULL to stored procedure variable that is varbinary(max). I use DbNull.Value in C# code. But error occurs. What should I do for send Null to stored procedure?
View 3 Replieshere my code-
using (SqlDataReader sqlDataReader = ExecuteReader(CommandType.StoredProcedure, StoredProcedures.AuthenticateUser, sqlParameter))
{
isAuthenticated = Convert.ToBoolean(sqlParameter[2].Value);
if (isAuthenticated)
{
if (sqlDataReader.Read())
{
User = new UserEntity();
DbHelper.GetEntity(sqlDataReader, User);
}
}
}
I always get isAuthenticated as false because sqlParameter[2].Value is null all the time but when I execute my query with ExecuteNonQuery it gives me the value but in that case I can not fill the entity.
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?
I have a SQL stored procedure on a SQL server that uses a variable to create a new database. This works in SQL I have a connection string that works I have added a sqldatasource to a webform, and configured the datasource to connect to the remote SQL database and am able to select my stored procedure. I am able to bind the variable to a control in the sqldatasource wizard (often two of the same control appear- if that is significant)
When I test the sqldatasource in the configuration wizard and put in a manual entry, the procedure runs and works
It is on the web page I am having troubles. I want the user to enter a name in a text box, click a button/link, and run the procedure. Ive used buttons and links...but not in the right way I think, becuase when I click them the stored procedure doesnt run.
As mentioned above, I have selected the right control in the sqldatasource wizard. So what do I need to do now?
Is it button click event? If so whats the syntax?
Should I be embedding my textbox in a dataview?
My stored procedure expects a uniqueidentifier as a parameter. However, this unique identifier is stored in a table variable, which looks like this:
DECLARE @GiftGuid uniqueidentifie
I created a database on SQL Server 2005 developer edition with MS SQL Server Mangement Studio Express. Now I created code that accessess a stored procedure named 'pS_TableData'. However I have one problem the code raises an exception "Could not find stored procedure 'pS_TableData 'Letters''."the code looks like this:
[Code]....
Note I am using (for now) the same connection that I used to create my database so in essence my web site is the administrator. I tried a simple SELECT from a table with the same connection and it raised no exceptions!When I run EXECUTE ps_TableData 'Letters' in SSMS it has no . I also tried EXECUTE ps_TableData 'Letters' as the command paramated for SqlCommand but it had the same effect
I call this stored procedure with a dataset. It always returns a 0 but should return a 1 or 2. Does anybody know why it isn't returning a 1 or 2.
ALTER PROCEDURE dbo.sp_InsertLinkVote
@LinkId int,
@LinkVoter nvarchar(50),
@LinkVotesDateTime datetime,
@LinkVotesGoodBad bit
AS
/* SET NOCOUNT ON */
If EXISTS
(SELECT
*
FROM
LinkVotes
WHERE
LinkId=@LinkId
and
LinkVoter = @LinkVoter )RETURN
1
ELSE
INSERT INTO
dbo.LinkVotes (LinkId,LinkVoter,LinkVotesDateTime,LinkVotesGoodBad)VALUES
(@LinkId,@LinkVoter,@LinkVotesDateTime,@LinkVotesGoodBad)RETURN
2
Here is were a call it in my code behind.
Dim VoteCheck
As
Integer
Dim InsertVote
As
New DataSetStoredProceduresTableAdapters.QueriesTableAdapter
VoteCheck = InsertVote.sp_InsertLinkVote(LinkId, User.Identity.Name, DateAndTime. Now,
True)
'0 is a good vote.
If VoteCheck = 0
Then
It seems to insert and not insert correctly, I just cannot get it to return the correct value.
I am developing a page in ASP.Net where I have the user enter a letter into a textbox. They then click a submit button and I all of the userids that start with that letter are to be displayed. So I need the SP to go through the database and match first letters with what was entered. Here is what I have as my code for the SP:
CREATE PROCEDURE dbo.exp2 @testchar Char(1)
declare @flag as char(2)
set @flag = @testchar + '%'
select userid
from ex_database
where userid like @flag
So I'm working with SQL Server 2005 and I'm having trouble saving a stored procedure. (I've only used an Apache server before, and for nothing as complex as the project I'm working on)
Nonetheless, the problem is that I don't know where to actually save the stored procedure I create. In my Object Explorer, I go to the database I want to create it in, expand Programmability, right click on Stored Procedures and choose to create a new one. That's all nice, but when I'm ready to save and test it from my webpage, it asks where to save it. This is probably dumb of me, but I don't know where to. I try to save it on the desktop and click/drag to the Stored Procedures folder in my database, but all that does is open it, not actually store it in the database. Maybe I'm getting the point of stored procedures wrong? Let me know what I should do to save it in the right place so I can access it.