Select Statement Returns Null In Stored Proc
Feb 22, 2006
If I run this statement in Query Analyzer, it properly returns 1
for my testing table. But if I put the statement into a stored
procedure, the stored procedure returns NULL. What am I doing
wrong? I suspect it may be related to how I defined the parameters
for the stored procedure. Perhaps my definition of TableName and
ColumnName don't match what COLUMNPROPERTY and OBJECT_ID expect to
receive, but I don't know where to look for the function declarations
for those. Any pointers would be appreciated.
Select statement:
SELECT COLUMNPROPERTY(OBJECT_ID('Table1'), 'TestID', 'IsIdentity') AS
IsIdentity
Table definition:
CREATE TABLE [dbo].[Table1] (
[TestID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
Stored Procedure definition:
CREATE PROCEDURE spTest
(@TableName varchar,
@ColumnName varchar)
AS SELECT COLUMNPROPERTY(OBJECT_ID(@TableName), @ColumnName,
'IsIdentity') AS IsIdentity
View 2 Replies
ADVERTISEMENT
Mar 1, 2005
I am using SQL sever 2k and C#.
There is a stored-procedure that it has multiple select statements as returned result set. How can I use SqlCommand.ExecuteReader to get all result set?
What if the multiple select statements is " FOR XML", how can I set all xml using ExecuteXmlReader?
I tried to use ExecuteReader or ExecuteXmlReader, but seems that I can only get back the result set of the first select statement, all others are messed up.
stored procedure example: NorthWind database:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
Create PROCEDURE dbo.getShippersAndEmployeesXML
AS
select * from Shippers for xml auto, elements
select * from Employees for xml auto, elements
RETURN @@ERROR
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
C# code example:
//set connect, build sqlcommand etc
XmlTextReader reader = (XmlTextReader)command.ExecuteXmlReader();
StringBuilder sb = new StringBuilder();
while(reader.Read()) sb.Append(reader.ReadOuterXml());
Thanks for your help.
View 2 Replies
View Related
Feb 25, 2008
I am using SQL Server 2005 Developer on XP Home.
I have the following in one "New Query" window.
use test_01;
go
create table test_tbl_01 (
fname nvarchar(30),
lname nvarchar(30),
phone nvarchar(30),
another_field nvarchar(30));
go
insert into test_tbl_01 (fname, lname, phone, another_field)
values ('Susan', 'Johnstone', '555-0123', 'Some other data');
insert into test_tbl_01 (fname, lname, phone, another_field)
values ('Steven', 'Stonewall', '555-0124', 'More filler data');
insert into test_tbl_01 (fname, lname, phone, another_field)
values ('Bob', 'Otherguy', '555-0125', 'Just some text');
insert into test_tbl_01 (fname, lname, phone, another_field)
values ('Fred', 'Johnson', '555-6666', 'What ev');
insert into test_tbl_01 (fname, lname, phone, another_field)
values ('Carol', 'Jackson', '555-5432', 'You go girl!');
go
create procedure test_sp_01 @LName nvarchar
as
select *
from test_tbl_01 tt
where tt.lname like @LName + '%';
go
exec test_sp_01 @LName = 'John';
The result is:
Susan Johnstone 555-0123 Some other data
Fred Johnson 555-6666 What ev
Carol Jackson 555-5432 You go girl!
Why does the result include Carol Jackson? I expect that it should only include the two John....
More importantly, how can I get it to inlude only Susan Johnstone and Fred Johnson?
Thanks,
Gregory
View 3 Replies
View Related
Jan 21, 2004
hi,
i have the following query to sum the total due balance for a customer:
select sum(outstanding)from out where customer = 'myvariable' the problem is when the customer has no outstanding it returns NULL is there a way to return 0 when there are no outstanding?
thanks
View 7 Replies
View Related
May 9, 2008
I was comparing the parameters for two stored procs that I made using the SQL Server 2005 express management studio. Both of these sprocs only inserted one field into a single table. These were both of the type varchar.
One of the sprocs had "nocount on" and the other did not. I thought I would see the returns integer parameter in the sproc that did not have "nocount" set to on. I thought this is what returns an integer to validate an insert. Obviously, I am confused about how this works.
Can anyone help me to understand that difference between nocount on and the parameter that returns an integer.
Any help is appreciated.
View 1 Replies
View Related
Jan 8, 2004
I have a stored proc that inserts into a table variable (@ReturnTable) and then ends with "select * from @ReturnTable."
It executes as expected in Query Analyzer but when I call it from an ADO connection the recordset returned is closed. All the documentation that I have found suggests that table variables can be used this way. Am I doing somthing wrong?
View 1 Replies
View Related
Jun 6, 2006
I am using the following conditional select statement but it returns no results.
Declare @DepartmentName as varchar
Set @DepartmentName = null
Declare @status as bigint
Set @status = 4
IF (@DepartmentName = null) BEGIN
SELECT CallNumber AS [Call Number], Problem, Solution, Note
FROM AdminView
WHERE (Status = @status)
ORDER BY CallLoggedDT
END
ELSE IF (@DepartmentName <> null) Begin
SELECT CallNumber AS [Call Number], Problem, Solution, Note
FROM dbo.AdminView
WHERE (Status = @status) AND
(DepartmentName = @DepartmentName)
ORDER BY CallLoggedDT
end
when i run the 2nd half by itself it tells me to declare @status but not @departmentname. whats going on???
Chris Morton
View 3 Replies
View Related
Jan 6, 2006
I have a stored procedure like "select * from ThisTable"
I'm doing a dataread like:
Dim val as String = dateRead("column_from_ThisTable")
If the value in the column is not null everything works great, but if the value is null, instead of getting a value of "" which I expect, I get the column name??? In this case "column_from_ThisTable"
How do I make sure I get "" returned when the value in the column is db.null?
View 3 Replies
View Related
Jul 20, 2005
Does anyone know a select statement that would return the column namesand keys and indexes of a table?Thanks,TGru*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 3 Replies
View Related
May 18, 2006
Hello:
I need assistance writing a SELECT statement. I need data from a table that matches one (or more) of multiple criteria, and I need to know which of those criteria it matched. For instance, looking at the Orders table in the Northwind database, I might want all the rows with an OrderDate after Jan 1, 1997 and all the rows with a ShippedDate after June 1, 1997. Depending on which of those criteria the row matches, it should include a field stating whether it is in the result set because of its OrderDate, or its ShippedDate. One way of doing this that I've already tried is:
SELECT 'OrderDate' AS [ChosenReason], Orders.*FROM OrdersWHERE OrderDate > '1-1-1997'UNIONSELECT 'ShippedDate' AS [ChosenReason], Orders.*FROM OrdersWHERE ShippedDate > '6-1-1997'
In my application, scanning a table with thousands of records for five sets of criteria takes a few seconds to run, which is not acceptable to my boss. Is there a better way of doing this than with the UNION operator?
Thank you
View 2 Replies
View Related
Mar 31, 2008
Ok well i have a stored procedure that returns an integer between 1 and 5. My problem now is i want to check to see if the table will return NULL and if so i don't want to assign the value to my variable otherwise it'll trow an error. My code is listed below but i keep getting the error "Conversion from type 'DBNull' to type 'Integer' is not valid." i've also tried If getoptionpicked.Parameters("@optionpicked").Value = Nothing ThenIf getoptionpicked.Parameters("@optionpicked").Value Is system.dbnull Then below is the rest of the code If getoptionpicked.Parameters("@optionpicked").Value Is Nothing Then Else optionpicked = getoptionpicked.Parameters("@optionpicked").Value If optionpicked = 1 Then option1.Checked = True ElseIf optionpicked = 2 Then option2.Checked = True ElseIf optionpicked = 3 Then option3.Checked = True ElseIf optionpicked = 4 Then option4.Checked = True ElseIf optionpicked = 5 Then option5.Checked = True Else End If End If
View 1 Replies
View Related
Jun 6, 2007
I have a stored proc which should be returning a datatable. When I execute it manually it returns all requested results. However, when I call it via code (C#) it is returning a null table which leads me to believe the problem is in my code. I'm not getting any errors during runtime. Any help at all would be a BIG help!
private void PopulateControls() { DataTable table = CartAccess.getCart(); }
public static DataTable getCart() { DbCommand comm = GenericDataAccess.CreateCommand(); comm.CommandText = "sp_cartGetCart";
DbParameter param = comm.CreateParameter(); param.ParameterName = "@CartID"; param.Value = cartID; param.DbType = DbType.String; param.Size = 36; comm.Parameters.Add(param);
DataTable table = (GenericDataAccess.ExecuteSelectCommand(comm)); return table; }
public static DataTable ExecuteSelectCommand(DbCommand command) { // The DataTable to be returned DataTable table; // Execute the command making sure the connection gets closed in the end try { // Open the data connection command.Connection.Open(); // Execute the command and save the results in a DataTable DbDataReader reader = command.ExecuteReader(); table = new DataTable(); table.Load(reader); // Close the reader reader.Close(); } catch (Exception ex) { Utilities.SendErrorLogEmail(ex); throw ex; } finally { // Close the connection command.Connection.Close(); } return table; }
View 1 Replies
View Related
Jan 18, 2008
I'm in sql server mgmt 2005 and i highlighted a stored proc and clicked on "execute". I need to pass a null value in as the first param value to test this stored proc. what is the syntax, is below correct?
EXECUTE @RC = [SomeDataBaseName].[dbo].[sp_SomeStoredProcedure]
null
,'1234'
,1
View 3 Replies
View Related
May 29, 2008
i have a sp with the following sql statement inside it
select
name
from
customers
where
name
like
@ShopperName + '%'
--------------------------
If I paas to the store proc '%123' as the value for shoppername it will return all values in the table. Instead I should get all record starting with %123.
What could be a perfect solution
View 1 Replies
View Related
Mar 11, 2004
How can I do a null to 0 in a select statement? I tried the NZ() function but it is not part of SQL.
Thanks very much,
View 5 Replies
View Related
Nov 7, 2006
Hi All,
I'm haveing problems with a simple if statement within a stored proc. Here is a snippet of the stored proc
SELECT * FROM [tbl_jobs], [tbl_users] WHERE tbl_jobs.companyid = tbl_users.id IF @industry = 31 BEGIN AND industry = @industry END
The error message i get from enterprise manager is:
Error 156: Incorrect syntax near the keyword 'AND'
If i remove the if statement and select and select an 'industry' value other than 31 the it works fine.
Thanks
View 6 Replies
View Related
May 12, 2008
Hi All,
What i'm trying to do is build a dynamic query where the like clause is the variable bit of the query. What I've done is to create 4 varchar variables of length1000, and a variable to hold the result of the concatenated variables, which is defined as length of 4000. I've checked the length of the resultant query and its comes in at arount the 450 charcter lenght, but when I run the stored proc it truncates the @varfull around the point of the first % sign.
I've tried various ways to create this query and it always truncated around the point of the % sign.
Am I doing this right? can anyone point me in the right direction on how to do this with a sql statement, it works fine for text strings!
Here's part of the code, I create the content of @var3 in a loop based on a string of words passed into the stored proc.
Thanks for any help on this!
regrads
davej
@var1 = 'SELECT COUNT(d.item_id) AS Expr1, d.item_id FROM tp_index_details AS d INNER JOIN tp_index ON d.idx_id = tp_index.idx_id '@var2 = 'WHERE (d.idx_id IN (SELECT idx_id FROM tp_index AS i WHERE (item_Text LIKE'
@var3 = ''%london%' OR item_Text LIKE '%solicitor%''
@var4 = ' ) AND (subscription_id = 1000))) GROUP BY d.item_id ORDER BY d.item_id DESC'
@varfull = @var1+@var2+@var3+@var4
View 11 Replies
View Related
Jul 23, 2005
Grrr!I'm trying to run a script:print 'Declaring cursor'declare cInv cursor forward_only static forselectdistinctinv.company,inv.contact,inv.address1,inv.city,inv.state,inv.postalcode,inv.cmcompanyidfromdedupe.dbo.ln_invoice as invleft joindedupe.dbo.customerid as cidondbo.fnCleanString(inv.company) = cid.searchcowhere((inv.customerid is nulland cid.searchco is null)and (inv.date >= '01/01/2003' or (inv.date < '01/01/2003' andinv.outstanding > 0.01))and not inv.company is null)print 'Cursor declared'declare@contact varchar(75),@company varchar(50),@address1 varchar(75),@city varchar(30),@state varchar(20),@zip varchar(10),@cmcompanyid varchar(32),@iCount int,@FetchString varchar(512)open cInvprint 'cursor opened'fetch cInv into@company,@contact,@address1,@city,@state,@zip,@cmc ompanyidprint 'Cursor fetched @@Cursor_rows = ' + cast(@@cursor_rows asvarchar(5))All the prints are there to help me figure out what's going on!When I get to the Print 'Cursor fetched @@cursor_rows....the value is 0 and the script skips down to the close and deallocate.BUT, if I just highlight the Select...When section, I get over 2,000rows. What am I missing?Thanks.
View 6 Replies
View Related
Sep 4, 2007
Hi.
I have an sql table which contains a number and a name. I would like to create a select statement that will display these two fields in the format :
"number | name", but if there is a null value in the number it will display only the name and vice versa.
How can I do it ?
Any help is appreciated.
View 1 Replies
View Related
Nov 23, 2004
I am using @strsql to construct the where condition for a select query.
Can you please correct my syntax.
DECLARE @strsql nvarchar(2000)
IF @ProgNO <> '' then
strsql = WHERE ProgNO = @ProgNO
end if
If @ProjNO <> '' then
if strsql <> '' then
strsql = strsql & " and ProjNO =@ProjNO
ELSE
strsql = wHERE ProjNO =@ProjNO
END IF
END IF
Thank you very much.
View 9 Replies
View Related
Dec 31, 2004
I just want to do this (shortened example):
Select hours from GetBaseStoredProc '10/01/2004' Where ReasonId = 1
where GetBaseStoredProc is a stored procedure that takes a date parameter and contains a select sql statement like:
select reasonId, hours from my table where mydate = @myDate
(in reality, it's a much larger and more complicated statement, just using this as an example)
I guess I'm treating my storedProc like a view. If worse comes to worse, I suppose I could create a view (would rather not), but I'm wondering if what I want to do is possible, and I'm just not using the right syntax.
Many thanks - (a former Oracle dev)
View 2 Replies
View Related
Mar 11, 2004
i have a vb app that is retriving data from an sql db using ado. i have a qry save in my db. i want to select from this qry where x in (1,2,3,4). i don't know how many values i will be putting into my where in statment. it could be one value or 100 values. below is my code. what i would like to do is pass one paramater to my stored proc and then break it up and use it in my select.
my desired result is as follows
-----------------------------------------------
declare cnt int
declare TempFulLString nvarchar(5000)
declare TempStyleFID int
TempFulLString = @str_TempString
select * from QryPicking_Slip_Fill_Listview1 where stylefid in (
While TempFulLString <> ''
begin
cnt = InStr(1, TempFulLString, ',')
TempStyleFID = Left(TempFulLString, (cnt))
TempFulLString = Right(TempFulLString, (Len(TempFulLString) - cnt))
TempStyleFID + ','
end
)
-----------------------------------------------
View 3 Replies
View Related
Aug 23, 2007
I'm trying to call a stored proc with parameters without using EXEC statement and with only using a SELECT statement. I have the stored proc and need to call it from a 3rd party application which provides an interface to SQL server and does not support EXEC statements, but only SELECT statements. Is there a way this can be done?
Any enlightenment is appreciated.
View 4 Replies
View Related
Jun 16, 2006
I need to write a storedproc that receives the name of a table (as a string) and inside the stored proc uses select count(*) from <tablename>. The problem is the passed in tablename is a string so it can't be used in the select statement. Any ideas how I can do what I want?
TIA,
barkingdog
View 1 Replies
View Related
May 3, 2006
Hello.
I've built a sample CLR function with the following declaration....
CREATE FUNCTION GetManager(@DeptCode nvarchar(3))
RETURNS nvarchar(1000)
WITH RETURNS NULL ON NULL INPUT
AS
EXTERNAL NAME Assembly1.[ClassLibrary1.MyVBClass].MyManager
And it works as expected, except when I use NULL:
DECLARE @MyManager nvarchar(1000)
EXEC @MyManager = dbo.GetManager NULL
PRINT @MyManager
It returns the value "Unknown" as it would have for any unknown DeptCode, as-programmed.
I'm of the theory it should have returned NULL without actually firing the function? Or is this only for non-CLR items... or stored procedures, not functions?
View 3 Replies
View Related
Dec 22, 2006
I have a stored procedure with a SELECT statement, that retrieves 1 row.
SELECT name FROM tblNames WHERE nameID = "1"
I want all the NULL values in that row to be change in some default values.
How do I do this?
View 4 Replies
View Related
Aug 25, 2007
Hello! I have a field "End" in my database that is mapped as DateTime and allows nulls. Now I want to do a SQL-Select (in a SqlDataSource) like SELECT * FROM My_Table Where (([End] = @EndDate) OR ([End] = null)) @EndDate is a valid DateTime, but the second OR condition doesn't work. What is the best way to check if the [End]-field is empty or null? Thank you very much!
View 1 Replies
View Related
May 16, 2005
If I try to run the code below, and even one of the values in the INNER
JOIN statements is NULL, the DataReader ends up with zero rows.
What I need is to see the results even if one or more of INNER JOIN
statements has a NULL value. For example, if I want info on
asset# 2104, and there's no value in the DriverID field, I need the
rest of the data to display and just have the lblDriverName by
blank. Is that possible?
<code>
Sub BindSearchGrid()
Dim searchUnitID As String
Dim searchQuery As String
searchUnitID = tbSearchUnitID.Text
lblIDNum.Text = searchUnitID
searchQuery = "SELECT * FROM Assets " & _
"INNER JOIN Condition ON Condition.ConditionID = Assets.ConditionID " & _
"INNER JOIN Drivers ON Drivers.DriverID = Assets.DriverID " & _
"INNER JOIN Departments ON Departments.DepartmentID = Assets.DepartmentID " & _
"INNER JOIN AssetCategories
ON AssetCategories.AssetCategoryID = Assets.AssetCategoryID " & _
"INNER JOIN Store ON
Store.[Store ID] = Assets.StoreID WHERE RTRIM(Assets.[Unit ID]) = '"
& searchUnitID & "'"
Dim myReader As SqlDataReader
myReader = Data.queryDB(searchQuery)
While myReader.Read
If
Not IsDBNull(myReader("Store Name")) Then lblStrID.Text =
myReader("Store Name")
If
Not IsDBNull(myReader("AssetCategory")) Then lblAsstCat.Text =
myReader("AssetCategory")
If
Not IsDBNull(myReader("Condition Description")) Then lblCondID.Text =
myReader("Condition Description")
If
Not IsDBNull(myReader("DepartmentName")) Then lblDepID.Text =
myReader("DepartmentName")
If
Not IsDBNull(myReader("Unit ID")) Then lblUnID.Text = myReader("Unit
ID")
If
Not IsDBNull(myReader("Year")) Then lblYr.Text = myReader("Year")
If
Not IsDBNull(myReader("Make")) Then lblMk.Text = myReader("Make")
If
Not IsDBNull(myReader("Model")) Then lblMod.Text = myReader("Model")
If
Not IsDBNull(myReader("Mileage")) Then lblMile.Text =
myReader("Mileage")
If
Not IsDBNull(myReader("Vin Number")) Then lblVinNum.Text =
myReader("Vin Number")
If
Not IsDBNull(myReader("License Number")) Then lblLicNum.Text =
myReader("License Number")
If
Not IsDBNull(myReader("Name")) Then lblDriverName.Text =
myReader("Name")
If
Not IsDBNull(myReader("DateAcquired")) Then lblDateAcq.Text =
myReader("DateAcquired")
If
Not IsDBNull(myReader("DateSold")) Then lblDtSld.Text =
myReader("DateSold")
If
Not IsDBNull(myReader("PurchasePrice")) Then lblPrPrice.Text =
myReader("PurchasePrice")
If
Not IsDBNull(myReader("NextSchedMaint")) Then lblNSM.Text =
myReader("NextSchedMaint")
If
Not IsDBNull(myReader("GVWR")) Then lblGrVWR.Text = myReader("GVWR")
If
Not IsDBNull(myReader("GVW")) Then lblGrVW.Text = myReader("GVW")
If
Not IsDBNull(myReader("Crane Capacity")) Then lblCrCap.Text =
myReader("Crane Capacity")
If
Not IsDBNull(myReader("Crane Certification")) Then lblCrCert.Text =
myReader("Crane Certification")
If
Not IsDBNull(myReader("Repair Cost")) Then lblRepCost.Text =
myReader("Repair Cost")
If
Not IsDBNull(myReader("Estimate Replacement")) Then lblEstRep.Text =
myReader("Estimate Replacement")
If
Not IsDBNull(myReader("SalvageValue")) Then lblSalVal.Text =
myReader("SalvageValue")
If
Not IsDBNull(myReader("CurrentValue")) Then lblCurVal.Text =
myReader("CurrentValue")
If
Not IsDBNull(myReader("Comments")) Then lblCom.Text =
myReader("Comments")
If
Not IsDBNull(myReader("Description")) Then lblDesc.Text =
myReader("Description")
End While
End Sub</code>
View 1 Replies
View Related
May 22, 2006
hi all
i have a simple problem that i cant get through.
i am writing a stored procedure and i want to make this select statement
SELECT TOP 100 PERCENT dbo.tPA00175.chrJobNumber, dbo.tPA20802.dteDocumentDate, dbo.tPA00002.chrPhaseName, dbo.tPA00007.chrEmployeeNumber,
dbo.tPA20802.numActualQuantity, dbo.tPA20802.numChargeOutRate, dbo.tPA20801.numTotalCharges, dbo.tPA00125.numQTYInvoiced
FROM dbo.tPA00125 INNER JOIN
dbo.tPA00007 ON dbo.tPA00125.intEmployeeKey = dbo.tPA00007.intEmployeeKey INNER JOIN
dbo.tPA20802 ON dbo.tPA00125.intJobLineKey = dbo.tPA20802.intJobLineKey INNER JOIN
dbo.tPA20801 ON dbo.tPA20802.intTimesheetKey = dbo.tPA20801.intTimesheetKey INNER JOIN
dbo.tPA00002 ON dbo.tPA00125.intPhaseKey = dbo.tPA00002.intPhaseKey INNER JOIN
dbo.tPA00175 ON dbo.tPA00125.intJobKey = dbo.tPA00175.intJobKey
WHERE (dbo.tPA00125.numQTYInvoiced = 0)
ORDER BY dbo.tPA00175.chrJobNumber DESC
how would i go about setting my variables, if at all possible, from the values im calling for in the select statement?
is there another way of doing it rather than this
set @Phase=(select dbo.tPA00002.chrPhaseName
FROM dbo.tPA00125 INNER JOIN
dbo.tPA00007 ON dbo.tPA00125.intEmployeeKey = dbo.tPA00007.intEmployeeKey INNER JOIN
dbo.tPA20802 ON dbo.tPA00125.intJobLineKey = dbo.tPA20802.intJobLineKey INNER JOIN
dbo.tPA20801 ON dbo.tPA20802.intTimesheetKey = dbo.tPA20801.intTimesheetKey INNER JOIN
dbo.tPA00002 ON dbo.tPA00125.intPhaseKey = dbo.tPA00002.intPhaseKey INNER JOIN
dbo.tPA00175 ON dbo.tPA00125.intJobKey = dbo.tPA00175.intJobKey
WHERE (dbo.tPA00125.numQTYInvoiced = 0))
set @Resource=(select dbo.tPA00007.chrEmployeeName
FROM dbo.tPA00125 INNER JOIN
dbo.tPA00007 ON dbo.tPA00125.intEmployeeKey = dbo.tPA00007.intEmployeeKey INNER JOIN
dbo.tPA20802 ON dbo.tPA00125.intJobLineKey = dbo.tPA20802.intJobLineKey INNER JOIN
dbo.tPA20801 ON dbo.tPA20802.intTimesheetKey = dbo.tPA20801.intTimesheetKey INNER JOIN
dbo.tPA00002 ON dbo.tPA00125.intPhaseKey = dbo.tPA00002.intPhaseKey INNER JOIN
dbo.tPA00175 ON dbo.tPA00125.intJobKey = dbo.tPA00175.intJobKey
WHERE (dbo.tPA00125.numQTYInvoiced = 0))
set @hours=(select dbo.tPA20802.numActualQuantity
FROM dbo.tPA00125 INNER JOIN
dbo.tPA00007 ON dbo.tPA00125.intEmployeeKey = dbo.tPA00007.intEmployeeKey INNER JOIN
dbo.tPA20802 ON dbo.tPA00125.intJobLineKey = dbo.tPA20802.intJobLineKey INNER JOIN
dbo.tPA20801 ON dbo.tPA20802.intTimesheetKey = dbo.tPA20801.intTimesheetKey INNER JOIN
dbo.tPA00002 ON dbo.tPA00125.intPhaseKey = dbo.tPA00002.intPhaseKey INNER JOIN
dbo.tPA00175 ON dbo.tPA00125.intJobKey = dbo.tPA00175.intJobKey
WHERE (dbo.tPA00125.numQTYInvoiced = 0))
set @rate=(select dbo.tPA20802.numChargeOutRate
FROM dbo.tPA00125 INNER JOIN
dbo.tPA00007 ON dbo.tPA00125.intEmployeeKey = dbo.tPA00007.intEmployeeKey INNER JOIN
dbo.tPA20802 ON dbo.tPA00125.intJobLineKey = dbo.tPA20802.intJobLineKey INNER JOIN
dbo.tPA20801 ON dbo.tPA20802.intTimesheetKey = dbo.tPA20801.intTimesheetKey INNER JOIN
dbo.tPA00002 ON dbo.tPA00125.intPhaseKey = dbo.tPA00002.intPhaseKey INNER JOIN
dbo.tPA00175 ON dbo.tPA00125.intJobKey = dbo.tPA00175.intJobKey
WHERE (dbo.tPA00125.numQTYInvoiced = 0))
set @amount=(select dbo.tPA20801.numTotalCharges
FROM dbo.tPA00125 INNER JOIN
dbo.tPA00007 ON dbo.tPA00125.intEmployeeKey = dbo.tPA00007.intEmployeeKey INNER JOIN
dbo.tPA20802 ON dbo.tPA00125.intJobLineKey = dbo.tPA20802.intJobLineKey INNER JOIN
dbo.tPA20801 ON dbo.tPA20802.intTimesheetKey = dbo.tPA20801.intTimesheetKey INNER JOIN
dbo.tPA00002 ON dbo.tPA00125.intPhaseKey = dbo.tPA00002.intPhaseKey INNER JOIN
dbo.tPA00175 ON dbo.tPA00125.intJobKey = dbo.tPA00175.intJobKey
WHERE (dbo.tPA00125.numQTYInvoiced = 0))
set @date=(select dteDocumentDate
FROM dbo.tPA00125 INNER JOIN
dbo.tPA00007 ON dbo.tPA00125.intEmployeeKey = dbo.tPA00007.intEmployeeKey INNER JOIN
dbo.tPA20802 ON dbo.tPA00125.intJobLineKey = dbo.tPA20802.intJobLineKey INNER JOIN
dbo.tPA20801 ON dbo.tPA20802.intTimesheetKey = dbo.tPA20801.intTimesheetKey INNER JOIN
dbo.tPA00002 ON dbo.tPA00125.intPhaseKey = dbo.tPA00002.intPhaseKey INNER JOIN
dbo.tPA00175 ON dbo.tPA00125.intJobKey = dbo.tPA00175.intJobKey
WHERE (dbo.tPA00125.numQTYInvoiced = 0))
SET @invDate=(select(getdate()))
thanks alot
tibor
View 6 Replies
View Related
Sep 25, 2006
Hey,
I create a Select Statement in stored proc and I have printed the variable and it has the correct Select statement. My problem is now that I have the string I want how do I run it.
Thanks
View 2 Replies
View Related
Mar 27, 2008
Hi, i'm an SQL newbie. I'm trying to figure out if there's an easy way to take a single field record set from a SELECT statement and then do an INSERT using that record set, all in one single Stored Procedure.
Here's what i tried to do, but this returns an error "The name "phonenumber" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.".
The common field in both SELECT and INSERT is phonenumber.
quote:PROCEDURE [dbo].[usp_select_and_insert]
@name varchar(20),
AS
SELECT phonenumber FROM USERLIST where OWNERNAME LIKE @name
INSERT INTO LOGLOG (destination,content) values(phonenumber,'hello world');
GO
Hope that one of you can be kind enough to give me some guidance. Appreciate in advance. :)
View 1 Replies
View Related
Oct 15, 2007
I'm getting an error trying to add an sp that selects top @varname...I'm surprised since variables are typically allowed in t-sql selects.
Is this by design or am I doing something wrong? Do I have to build the select dynamically to accomplish this?
View 1 Replies
View Related
Feb 4, 2014
I wrote a select statement, I only want to see orders with max lastUpdatedOn date of 14 days and older. Now my results show dates with all orders of 14 days and older (which is OK), but all others are displayed in the "Uitgifte" column as "NULL". But those orders should not be displayed at all.
selectdistinct ProductionHeader.ProdHeaderOrdNr,
ProductionHeader.PartCode,
ProductionHeader.Description,
ProductionHeader.Qty,
(select max (ProdStatusLog.ProdStatusCode)
[code]...
View 8 Replies
View Related