If I have a stored proc which has 2 input variables and for example
the 2nd one is null or not provided what is the best way to turn this null
into a wildcard. If the second one is an int and not provided should I
but a checknull statement in the proc and convert this to a wildcard %. I believe if I do I need to change the where clause "where field1 = @var "
to where field1 like @var". Any input appreciated
Version: ASP.Net 2.0Language: C#Hello Everyone!I'm saving data from a function to an SQL Server 2000 db via a stored procedure. There are a few fields which are conditional and thus need not be passed to the procedure everytime. Under those circumstances I need to store null values in the fields.The problem is if I the value of the parameter as null, VS throws the error that a required parameter is not passed. I tried setting 'IsNullable' property of the parameter to true but it works for only those fields who are declared NULL in the stored procedure (For e.g. @DOB DATETIME = NULL).Strangely, this works well in classic ASP.Does anyone know how to accomplish this? Note, altering stored procedures is not an option for me.Thanks a lot!
View 4 Replies View RelatedWhat are the proper procedures to move a SQL 6.5 to another 6.5 (new box)
I need to move everything including stored procedures?
Any tips would be helpful.
Thanks,
Jason
Hi everyone I am fairly new to SQL server and need some help with creating backups. I am a little confused whether to append or overwrite my backup data when saving full, differential and transaction log backups to disk. Below is my current backup schedule.
1) A full backup every night at 2:00a.m. that overwrites my existing backup. Should this be an appended backup? Wouldn't my file keep growing? How do I manage the backup file size?
2) Differential backups every two hours that is set to append. How do I stop this file from growing? Should I be controlling the size of my backups through the backup set expiry option?
How do I incorporate transaction logs into my backup schedule and manage the size of the log? Should the log backups also be appended or overwriten?
I appreciate everyones help in advance.
I have a pivot transform that pivots a batch type. After the pivot, each batch type has its own row with null values for the other batch types that were pivoted. I want to group two fields and max() the remaining batch types so that the multiple rows are displayed on one row. I tried using the aggregate transform, but since the batch type field is a string, the max() function fails in the package. Is there another transform or can I use the aggragate transform another way so that the max() will work on a string?
-- Ryan
hi this is my stored procedure.i am passing mu column nam and recordname has to be fetched.if run this proceedure i am getting null records only.but i am having records in my table
CREATE PROCEDURE HRUser_spsearch
(
@columnname varchar(50),
@recordname varchar(50)
)
As
if(@columnname !=' ' and @recordname !=' ')
begin
select userid,user_name,password,role_code,expiry_date from usermaster where '+@columnname+' like '+@recordname+"%"'
end
GO
can any one help to solve this please
I need to somehow filter the results of my stored procedure to return the distinct "OrderNum" values. I'm using SQL database and I'm look for a way to alter the results from the stored procedure. My stored procedure rptOpenOrderLines currently returns all invoices (items under a OrderNum). I want to somehow filter those results to return one and only one of those "OrderNum" variables from the invoices. The tricky part is that I need to somehow find a way to do this without going into my database and directly altering the SQL stored procedure. I would be happy for any recommendations/ideas. Thanks!
View 3 Replies View RelatedHi, i am trying to return two values from SQL 2000 using a single stored procedure. The stored working fine in Query Analyser and returns the two values and two grids in the results window.
My problem is that when i execute the stored procedure using ADO.Net the dataset only has one of the values. e.g TId : 2, where it should read 'TId' : 2, 'ConfigPath': 'C:lah'
Please could anyone shed ligth on this problem?
here the code for the stored procedure:
CREATE PROCEDURE dbo.GetTillInfo
(
@TillIdR varchar(50),
@Password varchar(50)
)
AS
declare @TillId int
declare @configpath varchar(150)
IF Exists (SELECT Id FROM Tills WHERE TillRef=@TillId and TillPassword=@Password)
BEGIN
set @TillIdR = (SELECT Id FROM Tills WHERE TillRef=@TillId and TillPassword=@Password)
select @TillIdR as 'TId'
set @configpath = (SELECT configpath from customer,tills where
tills.customerid = customer.id and tills.id = @login)
select @configpath as 'ConfigPath'
END
ELSE
BEGIN
set @TillIdR = 0
select @TillIdR as 'TId'
set @configpath =''
select @configpath as 'ConfigPath'
END
GO
Hi All,i hv created a sp asCreate proc P @iClientid varchar (100)asBeginselect * from clients where CONVERT(VACHAR(100),iClientid) in(@iclientid)endwhere iclientid = int data type in the clients table.now if i pass @iclientid as @iclientid = '49,12,112'but this statement throws an conversion error ( int to char error).is there any way to fetch records from a select statement using astring???Thanks in Advance.
View 3 Replies View RelatedHow to return multiple values from stored procedures to reports in sql server 2005
View 5 Replies View RelatedHi
I am new to C# . I have a stored procedure which takes 4 parameters
GetSearchComplaint( Comp_ID , strViolator, strSts, FromDate, ToDate), These parameters can be null.
And i have 5 textboxes from which i send the parameters.
I am validating the input like this :System.Nullable<int> Comp_ID;
if ((txtsrchCompID.Text).Trim() == "")
{Comp_ID = null;
}else if ((txtsrchCompID.Text).Trim() == "")
{
try
{int i = int.Parse(txtsrchCompID.Text);
Comp_ID =i;
catch
{mesage += "Complaint ID is not valid";
}
}
When i run this i get this error ---'Use of unassigned local variable 'Comp_ID'
I get the same error for FromDate(DateTime) and ToDate(DateTime) . but not for string variables strViolator and strSts.
How do i pass the null value to the stored procedure? pls help..
I am trying to build an Sql page hit provider. I am having trouble getting a count back from the database. If I use ExecuteScalar it doesn't see any value in the returned R1C1. If I use ExecuteNonQuery with a @ReturnValue, the return value parameter value is always zero. Ideally I would like to use a dynamic stored proceudre if there are any suggestions for using them with C#. My table has rvPathName, userName and a date. I have the AddWebPageHit method working so I know data connection and sql support code in provider is working. I think the problem is either in how I am writing the stored procedures or how I am trying to retrieve the data in C#. Any help with this will be greatly appreciated.
View 5 Replies View Relatedmy stored procedure is
create procedure t1 (@a int,@b int,@c int,@d int,@e int,@f int)
as
begin
select no,name,department from emp where a = @a and b =@b orc =@c or
d = @d or e = @e or f = @f
end
my problem is while executing i may get null values as input to the stored procedure .
how to validate them ? any ideas .
are there any arrays.
if c and d are null then my condition would be
where a = @a and b =@b or e = @e or f = @f
how many loops shall i have to write.
I have the following stored proc. which i am using on the front end to get all the record from table:
if there are any fields it has anynull values in it i am getting error dbnull value error.
i have null value for ReviewerComment field, can you please tell me how to pass a "" if it is null, in the store proc only, to get all the fresh dat to front end before bnding it to the datagrid control.
CREATE PROCEDURE [dbo].[sp_displayrevws]
AS
select r.RevID,
rtrim(f.revwfunction) as revwfunction,
rtrim(u.uname) as uname,
CONVERT(varchar(10),r.Issued,101) as Issued,
r.ReviewerComment,
r.Response,
r.ModuleID,
rtrim(r.ModuleName) as modulename,
r.ReviewerUserID,r.RevFunctionid,r.Dispositionid
from TAB_ccsNetReviewers r, tabuname u, ccsfunctions f, ccsdisposition d where u.id = r.ReviewerUserID and r.RevFunctionid = f.id and r.Dispositionid = d.id and r.ModuleID = 1 order by r.RevID ASC
GO
Thank you very much.
Hi all,
In my SQL Server Management Studio Express (SSMSE), pubs Database has a Stored Procedure "byroyalty":
ALTER PROCEDURE byroyalty @percentage int
AS
select au_id from titleauthor
where titleauthor.royaltyper = @percentage
And Table "titleauthor" is:
au_id title_id au_ord royaltyper
172-32-1176
PS3333
1
100
213-46-8915
BU1032
2
40
213-46-8915
BU2075
1
100
238-95-7766
PC1035
1
100
267-41-2394
BU1111
2
40
267-41-2394
TC7777
2
30
274-80-9391
BU7832
1
100
409-56-7008
BU1032
1
60
427-17-2319
PC8888
1
50
472-27-2349
TC7777
3
30
486-29-1786
PC9999
1
100
486-29-1786
PS7777
1
100
648-92-1872
TC4203
1
100
672-71-3249
TC7777
1
40
712-45-1867
MC2222
1
100
722-51-5454
MC3021
1
75
724-80-9391
BU1111
1
60
724-80-9391
PS1372
2
25
756-30-7391
PS1372
1
75
807-91-6654
TC3218
1
100
846-92-7186
PC8888
2
50
899-46-2035
MC3021
2
25
899-46-2035
PS2091
2
50
998-72-3567
PS2091
1
50
998-72-3567
PS2106
1
100
NULL
NULL
NULL
NULL
////////////////////////////////////////////////////////////////////////////////////////////
I try to do an ADO.NET 2.0-VB 2005 programming in my VB 2005 Express to get @percentage printed out in the VB Form1. I read some articles in the websites and MSDN about this task and I am very confused about "How to Work with Output Parameters & Report their Values in VB Forms": (1) Do I need the Form.vb [Design] and specify its properties of the object and classes I want to printout? (2) After the SqlConnectionString and the connection.Open(), how can I bring the value of @percentage to the Form.vb? (3) The following is my imcomplete, crude draft code:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form1
Dim connectionString As String = "Data Source=.SQLEXPRESS;Initial Catalog=pubs;Integrated Security=SSPI;"
Dim connection As SqlConnection = New
SqlConnection(connectionString)
Try
connection.Open()
Dim command As SqlCommand = New SqlCommand("byroyalty", connection)
command.CommandType = CommandType.StoredProcedure
...................................................................
..................................................................
etc.
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
From the above-mentioned (1), (2) and (3), you can see how much I am lost/confused in attempting to do this task. Please help and give me some guidances and good key instructions for getting the output parameter printed out in the FORM.vb in my VB 2005 Express project.
Thanks in advance,
Scott Chang
I'm trying to handle a stored procedure parameter. What needs to happen is that a particular statement shouldn't be executed if the parameter is empty. However, when I try it I get the following error:Cannot use empty object or column names. Use a single space if necessary.So, what's the correct way of doing the following?IF @filename <> ""BEGIN UPDATE Experimental_Images SET contentType = @contentType, filename = @filename WHERE (id = @iconNo)END
View 1 Replies View RelatedHi all,
I put "Northwind" Database in the Database Explorer of my VB 2005 Express and I have created the following stored procedure in the Database Exploror:
--User-defined stored procedure 'InsertCustomer'--
ALTER PROCEDURE dbo.InsertCustomer
(
@CustomerID nchar(5),
@CompanyName nvarchar(40),
@ContactName nvarchar(30),
@ContactTitle nvarchar(30),
@Address nvarchar(60),
@City nvarchar(15),
@Region nvarchar(15),
@PostalCode nvarchar(10),
@Country nvarchar(15),
@Phone nvarchar(24),
@Fax nvarchar(24)
)
AS
INSERT INTO Customers
(
CustomerID,
CompanyName,
ContactName,
ContactTitle,
Address,
City,
Region,
PostalCode,
Country,
Phone,
Fax
)
VALUES
(
@CustomerID,
@CompanyName,
@ContactName,
@ContactTitle,
@Address,
@City,
@Region,
@PostalCode,
@Country,
@Phone,
@Fax
)
=================================================
In my VB 2005 Express, I created a project "KimmelCallNWspWithAdoNet" that had the following code:
--Form_Kimmel.vb--
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form_Kimmel
Public Sub InsertCustomer()
Dim connectionString As String = "Integrated Security-SSPI;Persist Security Info=False;" + _
"Initial Catalog=northwind;Data Source=NAB-WK-EN12345"
Dim connection As SqlConnection = New SqlConnection(connectionString)
connection.Open()
Try
Dim command As SqlCommand = New SqlCommand("InsertCustomer", connection)
command.CommandType = CommandType.StoredProcedure
command.Parameters.Add("@CustomerID", "PAULK")
command.Parameters.Add("@CompanyName", "Pauly's Bar")
command.Parameters.Add("@ContactName", "Paul Kimmel")
command.Parameters.Add("@ContactTitle", "The Fat Man")
command.Parameters.Add("@Address", "31025 La Jolla")
command.Parameters.Add("@City", "Inglewoog")
command.Parameters.Add("@Region", "CA")
command.Parameters.Add("@Counrty", "USA")
command.Parameters.Add("@PostalCode", "90425")
command.Parameters.Add("@Phone", "(415) 555-1234")
command.Parameters.Add("@Fax", "(415 555-1235")
Console.WriteLine("Row inserted: " + _
command.ExecuteNonQuery().ToString)
Catch ex As Exception
Console.WriteLine(ex.Message)
Throw
Finally
connection.Close()
End Try
End Sub
End Class
==============================================
I executed the Form_Kimmel.vb and I got no errors. But I did not get the new values insterted in the table "Custermers" of Northwind database. Please help and tell me what I did wrong and how to correct this problem.
Thanks in advance,
Scott Chang
I have a DTSX package which reads values from a fixed-length text file using a data reader and writes some of the column values from the file to an Oracle table. We have used this DTSX several times without incident but recently the process started inserting NULL values for some of the columns when there was a valid value in the source file. If we extract some of the rows from the source file into a smaller file (i.e 10 rows which incorrectly returned NULLs) and run them through the same package they write the correct values to the table, but running the complete file again results in the NULL values error. As well, if we rerun the same file multiple times the incidence of NULL values varies slightly and does not always seem to impact the same rows. I tried outputting data to a log file to see if I can determine what happens and no error messages are returned but it seems to be the case that the NULL values occur after pulling in the data via a Data Reader. Has anyone seen anything like this before or does anyone have a suggestion on how to try and get some additional debugging information around this error?
View 12 Replies View RelatedI want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!
View 11 Replies View RelatedI have SQL Server 2012 SSIS. I have Excel source and OLE DB Destination.I have problem with importing CustomerSales column.CustomerSales values like 1000.00,2000.10,3000.30,NotAvailable.So I have decimal values and nvarchar mixed in on Excel column. This is requirement for solution.However SSIS reads only numeric values correctly and nvarchar values are set as Null. Why?
CREATE TABLE [dbo].[Import_CustomerSales](
[CustomerId] [nvarchar](50) NULL,
[CustomeName] [nvarchar](50) NULL,
[CustomerSales] [nvarchar](50) NULL
) ON [PRIMARY]
Hi
can somebody explain me how I can assign a NULL value to a datetime type field in the script transformation editor in a data flow task.
In the script hereunder, Row.Datum1_IsNull is true, but still Row.OutputDatum1 will be assigned a value '0001-01-01' which generates an error (not a valid datetime). All alternatives known to me (CDate("") or Convert.ToDateTime("") or Convert.ToDateTime(System.DBNull.Value)) were not successful.
Leaving out the ELSE clause generates following error: Error: Year, Month, and Day parameters describe an un-representable DateTime.
If Not Row.Datum1_IsNull Then
Row.OutputDatum1 = Row.Datum1
Else
Row.OutputDatum1 = CDate(System.Convert.DBNull)
End If
Any help welcome.
Hi,
My query "select blah, blah, rank from tablewithscores" will return results that can legitimately hold nulls in the rank column. I want to order on the rank column, but those nulls should appear at the bottom of the list
e.g.
Rank Blah Blah
1 - -
2 - -
3 - -
NULL - -
NULL - -
At present the NULLs are at the top of the list, but I do not want my ranking in descending order. Any suggestions?
Thanks
Dan
Rather than the real code, here's a sample we came up with.
Here's the C# Code:
public class sptest : System.Web.UI.Page
{
protected System.Web.UI.WebControls.Label Label1;
private DataSet dtsData;
private void Page_Load(object sender, System.EventArgs e)
{
// Put user code to initialize the page here
string strSP = "sp_testOutput";
SqlParameter[] Params = new SqlParameter[2];
Params[0] = new SqlParameter("@Input", "Pudding");
Params[1] = new SqlParameter("@Error_Text", "");
Params[1].Direction = ParameterDirection.Output;
try
{
this.dtsData = SqlHelper.ExecuteDataset(ConfigurationSettings.AppSettings["SIM_DSN"], CommandType.StoredProcedure, strSP, Params);
Label1.Text = Params[0].Value.ToString() + "--Returned Val is" + Params[1].Value.ToString();
}
//catch (System.Data.SqlClient.SqlException ex)
catch (Exception ex)
{
Label1.Text = ex.ToString();
}
}
Here is the stored procedure:
CREATE PROCEDURE [user1122500].[sp_testOutput](@Input nvarchar(76),@Error_Text nvarchar(10) OUTPUT)AS
SET @Error_Text = 'Test'GO
When I run this, it prints up the input variable, but not the output variable.
Hi,
This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.
Thank you in advance for any help on this matter
I have an SSIS package that imports data from an Excel file, replaces any value in Excel that reads "NULL" to "", then writes the data to a couple of databases.
What I have discovered today, is I have two columns of dates, an admit date and discharge date column, and what I need to do is anywhere I have a null value in the discharge date column, I have to replace it with the value in the admit date column.
I have searched around online and tried a few things using the Replace funtion in Derived columns but no dice so far.
Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.
For example, even this simple little guy:
CREATE PROCEDURE BOB
AS
PRINT 'BOB'
GO
Gets created as a system stored procedure.
Any ideas what would cause that and/or how to fix it?
Thanks,
Jason
Hi, I ve a table, I want to fetch certain rows based on the value of a Column. That column is nullable, and contains NULL values.I used the following query,SELECT Col_A FROM TABLE1 WHERE SOME_ID = 1317 AND Col_B NOT IN (8,9) Here, Col_B contains NULL values too. I need to fetch all rows where Col_B is not 8 or 9.Now, if I use "NOT IN", it does not work. I tried reading on it and got to know why it does not work. Even "NOT EXISTS" does not help. But still I've to fetch my values. How do I do that?Thanks & Regards,Jahanzeb
View 6 Replies View RelatedI have tried doing a search, as I figured this would be a common problem, but I wasn't able to find anything. I know that my SP is functional because when I use VWD execute the query outside of the webpage, I get the correct results -however I have to ensure that a field is either entered, or set to <NULL>. In my SET's I want it to use the wildcards.
What I want is to do a search (plenty of existing topics on that, however none were of help to me). If a field is entered, then it is included in the search. Otherwise it should be ignored. In my VB I have the standard stored procedure call, passing in values to all of the parameters in the stored proc below:
CREATE PROCEDURE dbo.SearchDog@tagnum int,@ownername varchar(50), @mailaddress varchar(50),@address2 varchar(50),@city varchar(50),@telephone varchar(50),@doggender varchar(50),@dogbreed varchar(50),@dogage varchar(50),@dogcolour varchar(50),@dogname varchar(50),@applicationdate varchar(50)AS IF @tagnum=-1 SET @tagnum=NULL SET @ownername = '%'+@ownername+'%' SET @mailaddress = '%'+@mailaddress+'%' SET @address2='%'+@address2+'%' SET @city = '%'+@city+'%' SET @telephone='%'+@telephone+'%' SET @dogcolour='%'+@dogcolour+'%' SET @dogbreed='%'+@dogbreed+'%' SET @dogage='%'+@dogage+'%' SET @doggender='%'+@doggender+'%' SET @dogname='%'+@dogname+'%' SET @applicationdate='%'+@applicationdate+'%'
SELECT DISTINCT * FROM DogRegistry WHERE ( TagNum = @tagnum OR OwnerName LIKE @ownername OR MailAddress LIKE @mailaddress OR Address2 LIKE @address2 OR City LIKE @city OR Telephone LIKE @telephone OR DogGender LIKE @doggender OR DogBreed LIKE @dogbreed OR DogAge LIKE @dogage OR DogColour LIKE @dogcolour OR DogName LIKE @dogname OR ApplicationDate LIKE @applicationdate ) AND TagNum > 0GO
I don't know why it is creating links inside my SP -ignore them. TagNum is the primary key, if that makes a difference.
On the webpage, it ONLY works when every field has been filled (and then it will only return 1 row, as it should, given the data entered). Debugging has shown that when nothing is entered it passes "".
Any ideas?
I am trying to retrieve data from two different tables. One of the tables has more than 20 columns some of which are null. I would like to retrieve data from both tables excluding the columns which have null values. How do I do this?
View 3 Replies View RelatedWould this take care of null values in either a.asset or b.asset?
SELECT convert(decimal(15,1),(sum(isnull(a.asset,0))/1000.0)+(sum(isnull(b.asset,0))/1000.0)) as total_assets
What's throwing me off is that there are multiple a.asset or b.asset for each unique ID. It seems to work, but I'm not following the logic too well. If I were doing this in another language, I would loop through, summing a.asset and b.asset wherever it's not null for each unique ID.
Hi,
How can I use "Derived Column" to check if a Datetime value is null or not and if null to insert 00/00/00 instead. ?
The background being that while using a "Derived Column" to change a Column from a (DT_DATE) to a (DT_DBTIMESTAMP) everytime I get a null value it see's it as a error.
And the column in particular has ~ 37 K blank / null fields so Im getting a lot of errors
So far I have tried to use something like
ISNULL([Column 34])
Or
SELECT ISNULL(ID, '00/00/0000') FROM [Column 34]
Or
SELECT ISNULL(au_id, '00/00/0000 00:00') AS ssn
FROM [Column 34
but none seems to work [Column 34] being the offending column.
What a normally use is just a simple "(DT_DBTIMESTAMP)[Column 34]"
in the expression column, which seems to work well, but here I get alot of errors
Any ideas?
I set up a new SQL database file, in that file I allowed nulls, When I went through code to save the record, the exception is saying it doesnt allow nulls.
Before I get to involved with SQL, is it a bad practice to use nulls?
If it is what do you enter in place of the null value,
which will lead to more code, right?
Davids Learning SQL
hi ive got a inert sub where i grab values from text boxes etxthe values are passed to a stored procedure however , one of these fields is a date field , but the field is not required ...so on this line if the date text box is left blank i get an error , not a valid date .Parameters.Add("@actiondate", SqlDbType.DateTime).Value = txtActionDate.Texti have tried ( the actiondate field can take nulls ..)if txtActionDate="" then .Parameters.Add("@actiondate", SqlDbType.DateTime).Value = nothing else.Parameters.Add("@actiondate", SqlDbType.DateTime).Value = txtActionDate.Textend if but this doesnt workwhat is the best way of allowing blank values to be passed to the stored procedure( it doesnt fall over with normal text / varchar fields ) thanks
View 1 Replies View Related