I know it's unbelievable, but i didn't ever use SP before. Sorry for this noobish question, don't beat me for this - please ;-)
In my SELECT Query i like to have some (WHERE) paramteters le's say: ID, NAME and AGE. I allways programmicali generate a WHERE statement in relation to which parameters I really get, because not allwasy i get all the parameters together. This is fine for "normal" Tables.
How does this work with SP? If I don't get NAME, do I set the SP-Parameter NAME to null to unconsider it? Or do I have to make some IF statements in the SP to check, if NAME has some value?
Hi, I do have a stored procedure with parameters inside. I just want to ignore those parameters that are NULL so that my WHERE clause will not execute them anymore. Here's my code:
CREATE PROCEDURE SEARCHPATIENT @patnCode varchar(10) = NULL, @patnSurname varchar(20) = NULL, @patnGivenName1 varchar(20) = NULL AS SELECT.... FROM ... WHERE patnCode=@patncode AND patnSurname =@patnSurname AND patnGivenName1 =@patnGivenName1
BUT... If the user passes @patnSurname as NULL, I got an SQL error message: "There was an error executing the query.. Timeout expired... ". I was hoping I could write an WHERE clause where IT WILL ONLY EXECUTE those parameters WITH VALUE and IGNORE those NULLs.
Can someone help me on this? Can I use IF THEN ELSE statement inside the WHERE clause? Can I use CASE STATEMENT inside the WHERE clause?
How do you configure a multi-value parameter so it will allow the user to not enter the parameter?
I'm using integer multi-value parameters. I can't set the parameter to allow null, and when I preview the report and leave the parameter blank, it tells me to enter at least one value. I tried it with string parameters, and the report just doesn't run in preview if you don't enter any values, even if you choose "allow blank".
How do you specify a multi-value parameter to allow "empty" or "null"?
I am using a SQLDataSource with Stored Procedures. The Select, Insert and Update all work well. However I cannot get the delete to work. My stored procedures are tested and verified and the parameter names are the same as the source columns. When I try to run the delete an error that the stored procedure expects the parameter @locationStationId, however this value passes properly for the Update command?!? I tried to change the parameter to original_locationStationID to pass the original value, however this result in Null being passed for the parameter. I cannot understand why this works for Update and passes the location ID, but will not work for DELETE. Can anyone shed any light onto the matter? Thanks.OldValuesParameterFormatString="original_{0}" UpdateCommand="spUpdateLocation" UpdateCommandType="StoredProcedure" DeleteCommand="spDeleteLocation" DeleteCommandType="StoredProcedure"> <DeleteParameters> <asp:Parameter Name="locationStationId" Type="String" /> </DeleteParameters> <InsertParameters> <asp:Parameter Name="locationStationId" Type="String" /> <asp:Parameter Name="locationType" Type="String" /> <asp:Parameter Name="locationName" /> <asp:Parameter Name="division" Type="String" /> </InsertParameters>
I'm new to SQL Server, so if I'm doing anything stupid don't bemean :)I have a procedure that I use to return data based on optionalparameters. It works fine, except when the underlying data contains anull on one if the fields being searched.My system uses a default wildcard for all parameters, so this excludessuch records. I need a way to add in " OR fldName IS NULL " where theparameter is empty or '%'. I've looked at using CASE WHEN, but itdoesnt seem to like SQL Keywords being part of the WHEN clause.I'd hate to have to resort to executing concatonated strings made fromIF and ELSE statements. Just too messy and not at all pretty!Any Ideas? Here's what I've got:ALTER PROCEDURE [dbo].[procFindUnits]@strUnitIDnvarchar = '%',@strProjectNamenvarchar = '%',@strAddressnvarchar = '%',@strTenancynvarchar = '%',@strTenurenvarchar = '%'ASBEGINSET NOCOUNT ON;SELECTtblUnits.strUnitID,tblProjects.strProjectName,qryAddresses.Address_OneLine,lkpTenancyTypes.strTenancyType,lkpTenureTypes.strTenureTypeFROM tblUnits INNER JOINtblProjects ON tblUnits.intProjectID = tblProjects.intProjectIDLEFT OUTER JOINlkpTenancyTypes ON tblUnits.intTenancyType =lkpTenancyTypes.intTenancyType LEFT OUTER JOINlkpTenureTypes ON tblUnits.intTenureType =lkpTenureTypes.intTenureTypeID LEFT OUTER JOINqryAddresses ON tblUnits.strUnitID = qryAddresses.strUnitIDWHERE(tblUnits.strUnitID LIKE @strUnitID)AND (tblProjects.strProjectName LIKE @strProjectName)AND (qryAddresses.Address_OneLine LIKE @strAddress)AND (lkpTenancyTypes.strTenancyType LIKE @strTenancy)AND (lkpTenureTypes.strTenureType LIKE @strTenure)END
i have a form where filter information is going to be keyed/selected. the data from that form is copied into a struct regardless of whether each field has data in it or not (eg firstname may have been entered but not lastname, so the struct would contain firstname = 'john' and lastname = null) i then want to build an sql statement (not using stored procedures but just direct sql) from this struct data but obvioulsy can't just have a statement that says 'select ID from theTable where firstname='john' AND lastname=''" as this won't bring back the right info, if anything at all.
i normally build the statements like this
Code Block string sql = "select ID from MembersTemp where FirstName = '{0}'"; SqlCommand cmd = new SqlCommand(string.Format(sql, FirstName), _con); _con.Open(); SqlDataReader rdr = cmd.ExecuteReader(); List<int> result = new List<int>(); while (rdr.Read()) { etc....
so really, what's the best way of building an sql statement where some search parameters won't hold a value and therefore need ignoring.
also, whilst i'm here, i use viz studio 2005 and sql server 2005 (express??), so that does mean i'm using TSQL as well??
I have created a report with a stored proc which takes a parameter whose default value is null. Now when I run my stored proc in Enterprise Manager without any params I get 4 rows otherwise with proper parameter I get 2 rows.
Now I set the params value to allow for Null and blank values. When I run the report if I provide parameter value it works fine but if i try to run it without any param it shows an empty report while the stored proc running without params shows 4 rows.
I have earlier sucessfully created reports where stored procs had 4 params out of which 3 where defaulted to null. And so I set the allow nulls, Allow Blanks values to be true for those params in Rep services. So when I selected value for 1st param and ran the report I got the desired report. But this time I have only one param which can be null. But its not working.
I am using version 9.00.2047.00 SP1 of Visual Studio 2005.
Using ADO.NET, I have been unable to get the Execute SQL task to successfully return the value of an output parameter defined as varchar or nvarchar when the value is null. No other data types seem to have this problem, including the sql_variant data type.
The variables to which the three output parameters return their values have a data type of Object. The task runs fine when the integer or datetime parameters are used, and the variables can be identified as null using IsDBNull. But as soon as the nvarchar (or varchar) parameter is included, the task fails with this message:
"The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 1 ("@vc"): Data type 0xE7 has an invalid data length or metadata length."
I have seen a couple of postings that sound similar to this problem, but so far I have found no resolution. Any advice would be much appreciated.
I have a CLR function that throws an error if one of the parameters is NULL. Am I using the IsNullable tag correctly or am I supposed to do this another way? The function simply formats decimal values using .NET culture information and returns a string. Thanks very much for any help. -- Erik
I am trying to view my reporting service web site for the first time, but I've got this Error message "StringStartsWith can't accept null parameters" I am using Widows Vista and Report Manager and Report Server Web site has been set to Default web Site and Windows Server Identity.BuiltIn Account has been set to Local System and Web Service Identity.ReportServer and Report Manager has been set to Classic .Net App Pool.
I have an event: Private Sub SqlDataSourceIncome_Deleted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSourceIncome.Deleted Dim command As SqlClient.SqlCommand command = e.Command If command.Parameters("@nReturnCode").Value <> 0 Then DROPDEAD() End If That fires from: <DeleteParameters> <asp:Parameter Name="nDeletebyId" Type="Int64" /> <asp:Parameter Name="nOtherId" Type="Int64" /> <asp:Parameter Direction="Output" Name="nReturnCode" Type="Int64" /> <asp:Parameter Direction="Output" Name="nReturnId" Type="Int64" /> </DeleteParameters> End Sub
When I: GridViewIncome.DeleteRow(GridViewIncome.SelectedRow.RowIndex) But nReturnCode is ALWAYS NULL... I even did a stored procedure that just: ALTER PROCEDURE [dbo].[sp_nDeletebyId] @nReturnCode bigint output, @nReturnId bigint output AS SET @nReturnCode = 0 SET @nReturnId = 0 And STILL got nothing but the NULLS... the insert & update stuff works fine, with identical code... it's just the DELETED event that I can't seem to knock. Has anyone seen this before? The above sample stored proc did return 0 when executed one the server... and, BTW, the row is deleted!
I'm trying to have a default or null value in the dropdown list of the parameters on SSRS report. The dataset is bound with the Dynamics-AX 2009 AOT query. In the screen shot below you can see that I need a show All option in the dropdown list.
Hey. I need to substitute a value from a table if the input var is null. This is fine if the value coming from table is not null. But, it the table value is also null, it doesn't work. The problem I'm getting is in the isnull line which is in Dark green color because @inFileVersion is set to null explicitly and when the isnull function evaluates, value returned from DR.FileVersion is also null which is correct. I want the null=null to return true which is why i set ansi_nulls off. But it doesn't return anything. And the select statement should return something but in my case it returns null. If I comment the isnull statements in the where clause, everything works fine. Please tell me what am I doing wrong. Is it possible to do this without setting the ansi_nulls to off??? Thank you
set ansi_nulls off
go
declare
@inFileName VARCHAR (100),
@inFileSize INT,
@Id int,
@inlanguageid INT,
@inFileVersion VARCHAR (100),
@ExeState int
set @inFileName = 'A0006337.EXE'
set @inFileSize = 28796
set @Id= 1
set @inlanguageid =null
set @inFileVersion =NULL
set @ExeState =0
select Dr.StateID from table1 dR
where
DR.[FileName] = @inFileName
AND DR.FileSize =@inFileSize
AND DR.FileVersion = isnull(@inFileVersion,DR.FileVersion)
Hi all, I want to learn about sp with examples,so can any one give me the best urls regarding this.and i want to write single sp for all like (insert,delete and update)in a single sp.Please guide me. Thank You
Hi I use the next StoredProcedure in Access, Inserts Users to tblUsers: INSERT INTO tblUsers ( UserName, Password, RetypePassword, Email, Comments ) VALUES (@UserName, @Password, @RetypePassword, @Email,@Comments);
I am New to Sql Server. Now i have to write two stored procedures.
Here are my requirements. If any one help please.
1)People often ask me to do this as well... change the name of the underwriter. For Mortgage Network underwriters (different than MGIC underwriters) all you need to do is:
Update mnetwork..unw_Nola set underwriter='<underwriters username>' where LoanID='<LoanID>'
So, I need you to write a stored procedure that will do exactly that. If you don't enter a loanID or username, the stored procedure should tell you that it can't complete the task and why. Also, the list of underwriters can be found in:
select LoginName from mnetwork..unw_LoginLookup where UnderwriterName = '<Name on the email>'
So for this one, you would select where underwriterName='John Brennan'.
For most usernames, it's just first initial last name (jbrennan in this case). You can make the stored procedure to both, if you want. If you enter an underwriter name, then it will translate to the loginname. If you enter the login name, it will just use that. You don't have to do all of that if you don't want. Just make sure the procedure verifies that the username is correct (in the table) and that is enough.
I am using SQL Server 2005 now and I have a table with following columns. ID, FirstName, LastName, Email "ID" is the primary key (int) and is set auto generated (1 increment) I have a StoredProcedure to insert a new record. CREATE PROCEDURE Candidate_Create @FName nvarchar(255), @LName nvarchar(255), @Email nvarchar(255)ASINSERT INTO Candidate (FirstName, LastName, Email)VALUES (@FName, @LName, @Email)GO I want the ID to be returned as the same time when a new record is inserted, how can I do it ? Is it possible ?
Hi,I'm wodering if it's possible (and the correct syntax) to make a JOIN between a Table and a SP's result. This is my code, but it goes in error in the EXEC:1 SET ANSI_NULLS ON 2 GO 3 SET QUOTED_IDENTIFIER ON 4 GO 5 -- ============================================= 6 -- Author:Luca de Angelis 7 -- Create date: 22/08/2007 8 -- Description:Inserimento dei dati contabili nella tabella di log 9 -- ============================================= 10 CREATE PROCEDURE dbo.InsertIntoLog_dati_contabili 11 -- Add the parameters for the stored procedure here 12 @id_gestore tinyint 13 AS 14 SET NOCOUNT ON 15 BEGIN TRANSACTION 16 INSERT INTO CEL_log_dati_contabili(numero_telefonico, anno, mese, id_gestore, id_tipo_log) 17 SELECT CEL_traffico_temp.numero_telefonico 18 , CEL_traffico_temp.anno 19 , CEL_traffico_temp.mese 20 , @id_gestore as id_gestore 21 , 1 22 FROM CEL_traffico_temp 23 INNER JOIN 24 (EXEC dbo.CEL_SimConGestoreNoFilePeriodo @id_gestore, CEL_traffico_temp.anno + CEL_traffico_temp.mese) AS tabella 25 ON CEL_traffico_temp.numero_telefonico = tabella.numero_telefonico 26 27 INSERT INTO CEL_log_dati_contabili(numero_telefonico, anno, mese, id_gestore, id_tipo_log) 28 SELECT CEL_traffico_temp.numero_telefonico 29 , CEL_traffico_temp.anno 30 , CEL_traffico_temp.mese 31 , @id_gestore as id_gestore 32 , 2 33 FROM CEL_traffico_temp 34 INNER JOIN 35 (EXEC CEL_SimNelFileNoGestore @id_gestore) AS tabella 36 ON CEL_traffico_temp.numero_telefonico = tabella.numero_telefonico 37 38 INSERT INTO CEL_log_dati_contabili(numero_telefonico, anno, mese, id_gestore, id_tipo_log) 39 SELECT CEL_traffico_temp.numero_telefonico 40 , CEL_traffico_temp.anno 41 , CEL_traffico_temp.mese 42 , @id_gestore as id_gestore 43 , 3 44 FROM CEL_traffico_temp 45 INNER JOIN 46 EXEC CEL_SimNelFileNoUtente @id_gestore AS tabella 47 ON CEL_traffico_temp.numero_telefonico = tabella.numero_telefonico 48 49 IF @@error <> 0 50 BEGIN 51 ROLLBACK TRANSACTION 52 END 53 ELSE 54 BEGIN 55 COMMIT TRANSACTION 56 END Help me please...
Visual Studio 2008 Code VB I'm trying to create a stored procedure that will update a database table. I want to make sure that duplicate records are not inserted into the Database Table, so I used IF NOT EXISTS . With the below code I can update the table, however, you can not add additional rows to the table. Could someone tell me what is wrong, or how to fix it?
Thanks! losssoc ALTER PROCEDURE dbo.CaseDataInsert
AS BEGIN IF NOT EXISTS(SELECT ReportType,CreatedBy,OpenDate,Territory,Region,StoreNumber,StoreAddress,TiplineID,Status,CaseType,Offense FROM CaseData)INSERT CaseData(ReportType, CreatedBy,OpenDate,Territory,Region,StoreNumber,StoreAddress,TiplineID, Status,CaseType,Offense)VALUES(@ReportType,@CreatedBy,@OpenDate,@Territory,@Region,@StoreNumber,@StoreAddress,@TiplineID, @Status,@CaseType,@Offense)
To all, I looked at the MS-SQL pubs sample database and execute the example stored procedure reptq2 and I got 17 results set back. Where can I find an example using Visual Studio DataGrid or any means to get all these results from this SP.
AS BEGIN IF NOT EXISTS (SELECT * FROM CustomerDetails WHERE Name = @Name AND Number = @Number) BEGIN UPDATE CustomerDetails SET Number = @Number,Name = @Name,City=@City,SSN = @SSN where CustomerID = @CustomerID END ELSE BEGIN print 'CANNOT UPDATE' END END
This my storedproc. My problem is when i select customerID = 1 to update and if the same row having name = @name and number =@number Then the update should take place.
but if any other row other than CustomerID=1 having name=@name and number=@number Then the update is should not take place.
but The above stored procedure is not working like that.
so please some one help me with this. Thankyou Ramya.
lSQL = "SELECT * FROM OPENQUERY(liorder,'SELECT a.KF_ORDER_NO AS OrdNo, f.KU_NAME AS Customer, " & _ "a.KF_ORDER_POS AS Pos, a.KF_SCHEIB_NR AS Pane, a.KF_QTY AS Qty, d.BREITE*d.HOEHE/1000*a.KF_QTY AS SQM, " & _ "a.KF_QTY*d.SUM_NETTO AS Val, a.KF_FERT_QTY AS Done, d.BREITE*d.HOEHE/1000*a.KF_FERT_QTY AS DoneSQM, " & _ "a.KF_FERT_QTY*d.SUM_NETTO AS DoneVal FROM LIORDER.AUF_KOPF c, LIPROD.KAPA_AUS_FERT a, LIORDER.KUST_ADR f, " & _ "LIORDER.AUF_POS d WHERE (f.KU_VK_EK = 0) AND (a.KF_SCHR_NR = 12) AND (c.AUF_NR = a.KF_ORDER_NO) AND " & _ "(c.KUNR = f.KU_NR) AND (a.KF_ORDER_NO = d.AUF_NR) AND (a.KF_ORDER_POS = d.AUF_POS) AND (f.KU_NAME IS NOT NULL) " & _ "GROUP BY a.KF_ORDER_NO, f.KU_NAME, a.KF_ORDER_POS, a.KF_SCHEIB_NR, a.KF_QTY, d.BREITE, d.HOEHE, a.KF_FERT_QTY, d.SUM_NETTO')" Set RS = New ADODB.Recordset RS.Open lSQL, DB
Do While Not RS.EOF Select Case RS!Pane Case "0" Or "1" glassSQL = "SELECT IDNR, GL_BEZ FROM OPENQUERY(liorder, 'SELECT a.IDNR, a.GL_BEZ FROM LIORDER.GLAS_DATEN a, LIORDER.AUF_POS b " & _ "WHERE a.IDNR = b.GLAS1 AND b.AUF_NR = " & RS!OrdNo & " AND b.AUF_POS = " & RS!Pos & "')" Set glassRS = New ADODB.Recordset glassRS.Open glassSQL, DB
Mike writes "I have a string, >!~[99DC# 4.12$2251.% 1.63& 3.58'2150.(-2050.>/~]
and I would like to extract parts of it and send it to a SQL database. here is the layout and data that should be in each column. The Data column will be filled in automatically, but once that happens I would like the other columns to get their data from the Data column.
Data = >!~[99DC# 4.12$2251.% 1.63& 3.58'2150.(-2050.>/~] Display_Address = 99 CE = 4.12 TPK = 2251 Si= 1.63 C = 3.58 TPL = 2150 TPS = 2050
/* hi people im doing a task and im not sure about some things. could u check if what im doing is good plz.*/
The task is: Produce a list of all female employees who earn more than the average salary of the male employees in the company. Display employee number, first name and last name.
The table: emp Attributes: EMPNO, FIRSTNAME, LASTNAME, SEX, SALARY
What i came up with: selectempno, firstname, lastname fromemp whereavg(salary)>any(selectavg(salary) fromemp wheresex='M') group by sex havingsex = 'F'
Here is my code to connect, any help would be appreciated. I am a total noob at this.
############### Code
<?php $host="localhost"; // Host name $username=""; // Mysql username $password=""; // Mysql password $db_name="orderofb_test"; // Database name $tbl_name="orderofb_test.members"; // Table name
// Connect to server and select databse. mysql_connect("$host", "$username", "$password")or die("cannot connect"); mysql_select_db("$db_name")or die("cannot select DB");
// username and password sent from signup form $myusername=$_POST['myusername']; $mypassword=$_POST['mypassword'];
$sql="SELECT * FROM $tbl_name WHERE username='$myusername' and password='$mypassword'"; $result=mysql_query($sql);
// Mysql_num_row is counting table row $count=mysql_num_rows($result); // If result matched $myusername and $mypassword, table row must be 1 row
if($count==1){ // Register $myusername, $mypassword and redirect to file "login_success.php" session_register("myusername"); session_register("mypassword"); header("location:login_success.php"); } else { echo "Wrong Username or Password"; } ?>
This is the error message I receive.
############### Code
Warning: mysql_connect() [function.mysql-connect]: Access denied for user 'nobody'@'localhost' (using password: NO) in /home/orderofb/public_html/php/checklogin.php on line 11 cannot connect
I changed all the permissons to 777 and I still receive this error.
$tbl_name="orderofb_test.members"; // Table name
Is this the correct table name? Here is a pic of my phpmyadmin
Hi everyone!I tried to set my SqlDataSource's SelectCommandType to be a stored procedure. However the SqlDataSource failed to cache it. But if I just copy paste my stored procedure's content to my SqlDataSource's SelectCommand property, the cache just works. Is "StoredProcedure" as the "SelectCommandType" is not supported when caching the data? Or am I missing something here? Please help.
I am trying to write a function for some source to make a call out to and fill a RadioButtonList. I am running into a few problems though that I need assistance on. (I am new to DataSets) Here is the function to fill the RBL: 1 Private Function GetDataSet(ByVal QuestionID As Integer, ByVal QuestionType As Integer, ByVal LocaleID As Integer, ByVal GroupingNum As Integer) As DataSet 2 Dim cnn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString) 3 Dim cmd As New SqlCommand 4 cmd.CommandText = "usp_responses_sel" 5 cmd.CommandType = Data.CommandType.StoredProcedure 6 7 ' Fill usp_ with Parameters 8 cmd.Parameters.AddWithValue("QuestionID", QuestionID) 9 cmd.Parameters.AddWithValue("LocaleID", LocaleID) 10 cmd.Parameters.AddWithValue("GroupingNum", GroupingNum) 11 12 Dim da As New SqlDataAdapter 13 da.SelectCommand = cmd 14 Dim ds As New DataSet 15 da.Fill(ds, "response") 16 Return ds 17 End Function
So my issue is with line 15 [da.Fill(ds, "response")]. I pulled this function from somewhere else and am trying to tailor it to my needs. However, I do not understand what I need to do with this line and it keeps bombing out. I thought this references the DB Table but in my case, the SP has several tables joined together. Is this how I reference it from the calling source code? Please assist. Also, I am having problems understanding the binding process from the calling source. Here is my code that calls the function:1 Dim ds As DataSet = GetDataSet(CType(e.Item.DataItem("question_id").ToString, Integer), QuestionTypeID.Value, intLocale, 2) 2 rblResponses2.DataSource = ds 3 rblResponses2.DataBind()
What do I need to do with it from here and how can I work with it after it's bound? Thanks
