I want the following query to return a row even when table 'X' is empty. How would I do this?
SELECT TOP 1 @Var1, @Var2, @Var3 from X
The parameters @Var1, @Var2 and @Var3 are passed to the stored procedure in which the above query is included.
When table is empty, it reurn nothing. It only return a row when table is not empty.
I have a stored procedure that selects the unique Name of an item from one table.Â
SELECT DISTINCT ChainName from Chains
For each ChainName, there exists 0 or more StoreNames in the Stores. I want to return the result of this select as the second field in each row of the result set.
SELECT DISTINCT StoreName FROM Stores WHERE Stores.ChainName = ChainName
Each row of the result set returned by the stored procedure would contain:
ChainName, Array of StoreNames (or comma separated strings or whatever)
How can I code a stored procedure to do this?
I have a table. In the table I have different types of mailingaddresses. The addresses are linked by and ID. ex,addresses per ID 1ID address1 add21 add31 add4distinct Addresses in tableID address1 add21 add31 add4What I am trying to figure out is if this is possible. Is it possibleto figure out some how with a query that in the above example ID 1is missing add1. I am trying to populate a dropdown list in my codewith just the addresses that are left from the distinct list. I trieddoing an intersect something like this but that did not seem to work.SELECT typeFROM address_demoINTERSECTSELECT typeFROM address_demo AS address_demo_1WHERE progid = '12954892'I am wondering if I am moving in the right direction or if this is evenpossible......Thanks
View 1 Replies View RelatedHi all! I've been banging my head on this for way to long and decided it's time to reach out for help. I'll try to be complete and concise with this, but this will end up being lengthy most likely.
The crux of the issue is that I apparently do not have any control over what is returned from a stored proc via the SqlCommand.ExecuteNonQuery method. I want the procedure to explicitly return 0 if the proc was successful, otherwise it will return the error code generated. A value of -1 is consistently being returned when SET NOCOUNT ON is in place, and when not, a 1 is returned. This seems to be ignoring the fact that I have RETURN 0 at the end of the proc. Perhaps I'm missing something, but if I RETURN 0, I should get 0...here is the procedure:1 ALTER PROCEDURE [dbo].[epsp_EditCities]
2 @CityId int = null,
3 @City varchar(50),
4 @CreatedBy int,
5 @UpdatedBy int,
6 @ActionType varchar(1) = 'X',
7 @Identity int = 0 OUT,
8 @RowCount int = 0 OUT
9
10 AS
11 DECLARE @ERROR int -- Local @@ERROR
12
13
14 SET NOCOUNT ON
15 BEGIN TRAN
16
17 IF @ActionType = 'I' /* --- INSERT --- */
18 BEGIN
19
20 INSERT INTO [EnrollmentPrograms].[dbo].[Cities]
21 ([City]
22 ,[CreatedBy]
23 ,[UpdatedBy])
24 VALUES
25 (@City
26 ,@CreatedBy
27 ,@UpdatedBy)
28 SET @Identity = SCOPE_IDENTITY()
29
30 END
31
32 IF @ActionType = 'U' /* --- UPDATE --- */
33 BEGIN
34
35 UPDATE [EnrollmentPrograms].[dbo].[Cities]
36 SET [City] = @City
37 ,[UpdatedBy] = @UpdatedBy
38 WHERE CityId = @CityId
39 END
40
41 IF @ActionType = 'D' /* --- DELETE --- */
42 BEGIN
43
44 DELETE FROM [EnrollmentPrograms].[dbo].[Cities]
45 WHERE CityId = @CityId
46 END
47
48
49 -- Error checking (place this after every statement) --
50 SET @ERROR = @@ERROR
51 SET @RowCount = @@ROWCOUNT
52
53 IF @ERROR != 0 GOTO HANDLE_ERROR
54
55 COMMIT TRAN -- No Errors, so go ahead
56
57 RETURN 0
58
59 HANDLE_ERROR:
60 ROLLBACK TRAN
61 RETURN @ERRORAnd a fascinating tidbit is the results from this which shows the return value as 0:1 DECLARE@return_value int,
2 @Identity int--,
3 --@RowCount int
4
5 EXEC@return_value = [dbo].[epsp_EditCities]
6 @CityId = 1,
7 @City = N'Agoura Hills',
8 @CreatedBy = 1,
9 @UpdatedBy = 1,
10 @ActionType = N'U',
11 @Identity = @Identity OUTPUT--,
12 --@RowCount = @RowCount OUTPUT
13
14 SELECT@Identity as N'@Identity'--,
15 --@RowCount as N'@RowCount'
16
17 SELECTReturnValue = @return_value
And finally, here is the C# call that always returns -1:
retVal = cmd.ExecuteNonQuery();
Now, I've tried almost all variations and unit tests on the procedure that I could dream up. Commenting this out and putting this in etc....I still remain unable to fix the return code to 0 and get that result into my retVal var.
Any love? Much thanks in advance for spending time on my issue!
Cheers!
Wayne
Query to return values from XML field
create table #temp
(
id int identity (1,1)
,FieldSet XML
)
INSERT INTO #temp
VALUES ('<Fields> <Field Key="Column1" value="value1" > </Field> <Field Key="Column2" value="value2"> </Field> </Fields>')
,('<Fields> <Field Key="Column3" value="value3" > </Field> <Field Key="Column4" value="value4"> </Field> </Fields>')
SELECT * FROM #temp
Expected Output :
Id Column1Column2Column3Column4
1value1value2nullnull
2nullnullvalue3value4
Hi, I need a sql that returns the query result as comma seperated list of values, instead of several rows. Below is the scenario... Table Name - Customer Columns - CustomerID, Join DateSay below is the data of Customer table ...CustomerID JoinDate1 04/01/20052 01/03/20033 06/02/20044 01/05/20025 09/07/2005Now i want to retrieve all the customerid's who have joined this year. Below is the query that i use for this case.Select CustomerID from Customer where JoinDate between '01/01/2005' and GetDate()This gives the below result as two rows.CustomerID15But i need to get the result as '1,5' (comma seperated list of resulting values).Any help is highly appreciatedThanks in AdvanceRamesh
View 4 Replies View RelatedI have two tables I am working with, they are "Institutions" and "InstitutionOversights". The relationship is one-to-many.
The sample data is below.
Table one:
InstitutionID, InstName
------------------------
1 School Alpha
2 School Beta
3 School Charlie
4 School Delta
Table two:
InstitutionOversightID, InstitutionID, Type
------------------------------------------------
1 1 Accreditation
2 1 Verifcation
3 1 Old System
I would like a query to return the results in the following format:
InstitutionID, InstName, TypeList
-----------------------------------------------
1 School Alpha Accreditation, Verification, Old System
2 School Beta null
3 School Charlie null
4 School Delta null
I am using a table to store different size numbers for example:
Look at the picture below:
But I want this type of output look at the picture below:
How to sort the query to return greater than zero values to sort up and zeros go down.Â
I am using sql server 2008.
Hello,Our QA team have running a lot of test scripts (for automated regressiontesting), they run them on the different databases (Oracle/MS SQL).Several of those tests are dependent on the current date/time. In order tobeable to use them efficiently, we changed the current date/time on the QAdatabase server to a specific date/time before starting the scripts, so weare sure the test scripts always run in the same environment.Resetting the date/time of the database server gives us more and moreproblems (OS problems, backup/ virusscan, ...).It is possible to fix the problem with SYSDATE function on Oracle by settingFIXED_DATE init parameter.Is it possible to 'change' the current date/time on 'database' level,instead of on OS level for MSSQL2000?Do you know other means to do such things?Thanks in advance,Konstantin
View 2 Replies View RelatedThis is regarding Report Services 2005 designing the report in Visual Studio.
Currently I am using a query to populate 2 date selectors in the parameter section at the top of a report. The start and end dates are determined by a query which returns the values based on yet another parameter. If I change the value of the paramter which is supposed to be used by the date selectors they don't seem to feel the need to re-calc themselves. I have 3 other dependent param's in the same report which behave reasonably.
Has anyone seen this before or is there way to somehow force a parameter re-caluclation?
jSiegel
We are discovering that adding Force Order to a query is substantially increasing performance. Any issues around using this ?
Craig
I have another post here regarding SQL 2005 running a query 50% slower than on 2000. It was discovered that 2005 runs the query in series whereas 2000 runs it in parallel.
Even with "Cost Threshold For Parallelism" set to a default value – 0, 2005 still executes my query in series. Does anyone know how to force a query to run in parallel in SQL 2005. I specifically want to set it at the database level.
I have the following table:
Create Table Item(
I_Code NVarChar(40) Primary Key NOT NULL,
I_MatID NVarChar(40),
I_Name NVarChar(160),
I_BC nvarchar(20),
I_Company nvarchar(20),
I_CompanyFound nvarchar(20),
I_Info1 nvarchar(55),
I_Acquired nvarchar(35),
I_Info2 nvarchar(55),
I_Info3 nvarchar(55),
I_Date DateTime DEFAULT GetDate()
);
Create Index ind_Item_Name on Item(I_Name);
Create Index ind_Item_BC on Item(I_BC);
Create Index ind_Item_Company on Item(I_Company);
Create Index ind_Item_CompanyFound on Item(I_CompanyFound);
create Index ind_Item_i1 on Item(I_Company,I_CompanyFound);
create Index ind_Item_i2 on Item(I_CompanyFound,I_Company);
Now this query DOES NOT use index:
select I_Name, I_Code, I_MatID, I_BC, I_Company,I_Info1, I_Acquired, I_CompanyFound, 0 as I_Found
from Item
where (I_Company='102' or I_CompanyFound='102' )
While this one use:
select I_Name, I_Code, I_MatID, I_BC, I_Company,I_Info1, I_Acquired, I_CompanyFound, 0 as I_Found
from Item
where (I_Company='102' )
UNION
select I_Name, I_Code, I_MatID, I_BC, I_Company,I_Info1, I_Acquired, I_CompanyFound, 0 as I_Found
from Item
where (I_CompanyFound='102' )
Both return the same rows. Is this a bug? I found the following:
http://support.microsoft.com/kb/223423
Some feedback?
Thanks
Hi,
I have a big table and want to make a plausibility check of it´s data.
Problem is, that my query stops, if there is an unexpected datatype in one of the rows. But that is it, what i want to filter out of my table with that query and save the result as new correct table.
How can i write a parameter to my query SQL Code, that if a error occurs, the querry resumes and the error line will not displayed in my final querry overview?
In my books and on the net, i don´t found something to this theme ;-(.
Thx in advance.
Select A.* from A inner join B on ( A.ID= B.ID )
I know there is some key word that you use to force SQL server to generate a new query plan ?What can that be ?
Hello all.
I have the following table
Create Table Item(
I_AssetCode NVarChar(40) Primary Key NOT NULL,
I_Name NVarChar(160),
I_BC nvarchar(20),
I_Company nvarchar(20)
);
Create Index ind_Item_Name on Item(I_Name);
Create Index ind_Item_BC on Item(I_BC);
Create Index ind_Item_Company on Item(I_Company);
It is populated with 50 000 records.
Searching on indexed columns is fast, but I've run into the following problem:
I need to get all distinct companies in the table.
I've tried with these two queries, but they both are very slow!
1. "select I_Company from item group by I_Company " - This one takes 19 seconds
2. "select distinct(I_Company) from item" -This one takes 29 secons
When I ran them through the SQL Management Studio and checked the performance plan, I saw that the second one doesn't use index at all ! So I focused on the first...
The first one used index (it took it 15% of the time), but then it ran the "stream aggregate" which took 85% of the time !
Actully 15% of 19 seconds - about 2 seconds is pretty much enough for me. But it looks that aggregate function is run for nothing!
So is it possible to force the query engine of the SSCE not to run it, since there is actually no aggregate functions in my select clause?
According to SQL CE Books online:
Group By
"Specifies the groups (equivalence classes) that output rows are to be placed in. If aggregate functions are included in the SELECT clause <select list>, the GROUP BY clause calculates a summary value for each group."
It seems the aggregate is run every time, not only when there is an aggregate function.
Is this a bug?
Thanks in advance,
TipoMan
How do you get the return value(in ASP) of @@identity in a stored procedure while inserting a row?
View 2 Replies View RelatedUsing ado, the _CommandPtr::Execute method returns a recordset. When calling a storedproc with this method, does the 'return' statement return the value in a recordset ?
EG:
//c++ ADO
_CommandPtr pCmd(__uuidof(Command));
............
_RecordsetPtr pRs = pCmd->Execute(NULL, NULL, adCmdStoredProc);
// Stored proc
CREATE PROCEDURE usp_my_proc
AS
SELECT .... FROM tbMyTable WHERE x = something
IF(@@ERROR <> 0)
return -1
.....
What in the case of an error would be returned to pRs recordset?
Any thoughts?
Mike B
hello,
is it possible to return a return value as a numeric datatype
I can successfully return as an integer, but when i change it to numeric i get this error
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
[Microsoft][ODBC SQL Server Driver]Invalid scale value
/output.asp, line 15
my output value in my asp code is thus
Command1.Parameters.Append Command1.CreateParameter("@RETURN_VALUE", 131, 4)
thanks
Afrika
hello,
is it possible to return a return value as a numeric datatype
I can successfully return as an integer, but when i change it to numeric i get this error
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
[Microsoft][ODBC SQL Server Driver]Invalid scale value
/output.asp, line 15
my output value in my asp code is thus
Command1.Parameters.Append Command1.CreateParameter("@RETURN_VALUE", 131, 4)
thanks
Afrika
I have calling a stored procedure that returns two values, and I want to catch these values and to store them into a variable.
Here is a piece of my SP inside SQL Server that shows the returned values:
…
SELECT @Id = SCOPE_IDENTITY()
SELECT @Id AS user_id
SELECT 1 AS Value
END
GO
In my aspx page I am trying to call the first value like this:
Dim nID
CmdInsert.Parameters.Add(New SqlParameter("@RETURN_VALUE", SqlDbType.bigint, 8, "user_id"))
CmdInsert.Parameters("@RETURN_VALUE").Direction = ParameterDirection.ReturnValue
CmdInsert.Parameters("@RETURN_VALUE").Value = nID
And to check if the right value is returned I use:
strConnection.open()
cmdInsert.ExecuteNonQuery
'Set the value of a textbox
ident.text = nID
strConnection.close()
But now no value appears in the textbox, How can I achieve it? What is wrong?
I have a situation where I need two values (both are integers) returned from a stored procedure. (SQL 2000)
Right now, I use the statement "return @@Identity" for a single value, but there is another variable assigned in the procedure, @NewCounselingRecordID that I need to pass back to the calling class method.
I was thinking of concatenating the two values as a string and parsing them out after they are passed back to the calling method. It would look something like "21:17", with the colon character acting as a delimiter.
However, I feel this solution is kludgy. Is there a more correct way to accomplish this?
Thanks in advance for your comments.
First question :
How can I set the return value of READTEXT to a variable of type nvarchar.
Second question :
I have a table t1 with a ntext column n1.
The ntext column has has words separated by empty space.
Each word can be assumed to be of size <= 255 characters.
How can I extract all the keywords in the ntext column to a table t2 with a column word nvarchar(255).
Assume that the text in column n1 is big enough so that it cannot be cast into a nvarchar or any other simpler type.
Any help on this is greatly appreciated.
Please do provide a sample code.
Alok.
I need to Return values with hierachy sum.
I have provided the tables with data and result expected below
============
CREATE TABLE
============
CREATE TABLE [dbo].[Travel_Master](
[Load_Id] [int] NULL,
[Mode_Id] [nchar](2) NULL,
[Mode_Info] [nchar](10) NULL,
[Has_Nodes] [nchar](3) NULL
) ON [PRIMARY]
[Code] ....
The parent data will have either QA or QC
Child will have data in QY.
========================
EXPECTED RESULT
========================
Mode_Info |Mode_Detail| QA| QC|QY
Air||4 | |
Sea|||5|
SEA|SHIP|||15
SEA|BOAT|||25
ROAD|||2 |
ROAD|BUS|||20
ROAD|BIKE|||30
ROAD|CYCLE|||4
ROAD|TRAM|||10
ROAD|CAR|3||
ROAD|BMW|||36
ROAD|AUDI|||84
I am trying to return all the names of employees and their managers
this query returns all the employees except for 1
SELECT E.FNAME,E.LNAME,M.FNAME,M.LNAME
FROM EMPLOYEE E,EMPLOYEE M
WHERE E.SUPERSSN=M.SSN
the one that isn't returned has a null SUPERSSN, but when I add in:
OR E.SUPERSSN IS NULL
it returns a row with the name of the employee whose SUPERSSN is null 8 times (where each time the M.FNAME,M.LNAME are other employee names)
How do I ammend the first query to return each employee and their respective manager once, the employee without a manager having null values for the manager name columns?
I am new to SQL and I am trouble-shooting a problem with a home-grown app someone else wrote using PERL. It has a web interface with names of boards. I found the program where i need to add the board names into and did that. The new board names show up in the drop-down list in the Web page for the app. Alerts are sent to the new board names and show up on the new boards. Users are granted access to the new boards and they can clear items off the new boards. Yet when i try to use a report function for the new boards i added, nothing is returned. I even ran a simple SQl select statement specifying the new board names and nothing is returned. The older board names, some of which i added myself, return values. I don't know what is going on. Any help is appreciated.
View 3 Replies View RelatedHi,
How do I ensure that DISTINCT values of r.GPositionID are returned from the below??
Code Snippet
SELECT CommentImage AS ViewComment,r.GPositionID,GCustodian,GCustodianAccount,GAssetType
FROM @GResults r
LEFT OUTER JOIN
ReconComments cm
ON cm.GPositionID = r.GPositionID
WHERE r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)
ORDER BY GCustodian, GCustodianAccount, GAssetType;
Thanks.
Hi,
I have two tables. Both tables have a FirstName and a LastName field. I can return the results where (LastName = LastName) and (FirstName = FirstName). However in one table some of the FirstName values are shortened ie Pat is P etc.
Is there some way (its prob using LIKE but i can't get it right) I can write the below query so that it returns values where the first letter of FirstName in one table equals the first letter of FirstName in the second table.
SELECT tbl2.FirstName, tbl2.LastName, SALARY.[Employee No]
FROM SALARY INNER JOIN tbl1 ON (SALARY.LastName = tbl2.LastName) AND (SALARY.FirstName = tbl2.FirstName)
Cheers for all help.
This is a portion of my stored procedure in T-SQL
CREATE PROCEDURE GetPoll
(
@PollID INT,
@PollQuestion VARCHAR(1000) Output,
@PollOptions VARCHAR(1000) Output,
@PollPercentages DECIMAL Output
)
AS
SELECT @PollQuestion = (SELECT PollQuestion FROM Polls WHERE PollID = @PollID)
SELECT @PollOptions = (SELECT [Option] FROM PollOPtions WHERE PollID = @PollID)
*SELECT @PollPercentages = (SELECT [Option], COUNT([Option]) As Num_Votes FROM Votes WHERE PollID = 1 GROUP BY [Option])
The final part(*) of this SQL will return more than one value, so is it possible for me to return all that information in one varaible with one SELECT query?? Or would I need to use a loop and query a value at a time, and store it into a delimited string?
thx in advance...
Hello Group
I am new to stored procedure and I need some assistants. I am using the following stored procedure and I would like the return the fldPassword and fldFullName values. Both fields are in the same table. What I am trying to do is display the uses full name (i.e. Welcome <full Name>) and with the password I want to check that the password is not the default password if it is then do a redirect to the change password page.
Thank you
Michael
CREATE PROCEDURE stpMyAuthentication
(
@fldUsername varchar( 50 ),
@fldPassword Char( 25 ),
@fldFullName varchar( 75 ) OUTPUT
)
As
DECLARE @actualPassword Char( 25 )
SELECT
@actualPassword = fldPassword
FROM [tbUsers]
Where fldUsername = @fldUsername
IF @actualPassword IS NOT NULL
IF @fldPassword = @actualPassword
RETURN 1
ELSE
RETURN -2
ELSE
RETURN -1
GO
code
Sub Login_Click(ByVal s As Object, ByVal e As EventArgs)
If IsValid Then
If MyAuthentication(Trim(txtuserID.Text), Trim(txtpaswrd.Text)) > 0 Then
FormsAuthentication.RedirectFromLoginPage(Trim(txtuserID.Text), False)
End If
End If
End Sub
Function MyAuthentication(ByVal strUsername As String, ByVal strPassword As String) As Integer
Dim strFullName As String
' Variable Declaration
Dim myConn As SqlConnection
Dim myCmd As SqlCommand
Dim myReturn As SqlParameter
Dim intResult As Integer
Dim sqlConn As String
' Set conn equal to the conn. string we setup in the web.config
sqlConn = ConfigurationSettings.AppSettings("sqlDbConn")
myConn = New SqlConnection(sqlConn)
' We are going to use the stored procedure setup earlier
myCmd = New SqlCommand("stpMyAuthentication", myConn)
myCmd.CommandType = CommandType.StoredProcedure
' Set the default return parameter
myReturn = myCmd.Parameters.Add("RETURN_VALUE", SqlDbType.Int)
myReturn.Direction = ParameterDirection.ReturnValue
' Add SQL Parameters
myCmd.Parameters.Add("@fldUsername", strUsername)
myCmd.Parameters.Add("@fldPassword", strPassword)
myCmd.Parameters.Add("@fldFullName", strFullName)
' Open SQL and Execute the query
' Then set intResult equal to the default return parameter
' Close the SQL connection
myConn.Open()
myCmd.ExecuteNonQuery()
intResult = myCmd.Parameters("RETURN_VALUE").Value
Session("strFullName") = strFullName
myConn.Close()
Response.Write(strFullName)
' If..then..else to check the userid.
' If the intResult is less than 0 then there is an error
If intResult < 0 Then
If intResult = -1 Then
lblMessage.Text = "Username Not Registered!<br><br>"
Else
lblMessage.Text = "Invalid Password!<br><br>"
End If
End If
' Return the userid
Return intResult
End Function
How can I return values from my SQL view back into my aspx page and shove each returned value into variables I define for certain returned fields?
View 2 Replies View RelatedCan someone please reply w/ example syntax on how to receive calculated variables from an invoked SP. DESCRIPTION:
sp_caller invokes sp_calc_values (passing @var1, @var2) via:
exec sp_calc_values @var1, @var2
sp_calc_values receives @var1, @var2 then calculates @var3, @var4
HOW does sp_caller receive the calculated values @var3, @var4 ???
thx in advance
Greetings,
I've search around quite extensively on the net and found a few examples that touch on this subject, but the only definitive one that seemed to solve this problem used a temp table in the UDF, which, to my knowledge, is impossible...
The problem is thus:
I want to create either a stored procedure or a user defined function to return a list of values I can intersperse to use in a WHERE AccountID IN (<values>). This way, if someone were to create a new stored procedure and they wanted to either only select accounts with those IDs or perform a NOT IN and use it to filter.
The Solution I'm attempting:
My idea is best represented in psuedo-code:
- Create a Function that stores all account Ids we relate to a particular account type, in this case, let's say accountsids "100, 101, 102, 407" are all accounts we want to consider "cash".
- The function would look something like:
CREATE FUNCTION CashAccountIDs()
RETURNS TABLE
AS
BEGIN
DECLARE TABLE @t1 (account INT)
INSERT INTO @t1 VALUES (100)
INSERT INTO @t1 VALUES (101)
INSERT INTO @t1 VALUES (102)
INSERT INTO @t1 VALUES (407)
RETURN @t1
END
Then I could call this function by doing something such as:
SELECT *
FROM Accounts
WHERE AccountId IN (dbo.CashAccountIds())
I would presumably do this for other collections of accounts as well, so that I would end up with say 5 functions I could call to filter various types of accounts.
Not too certain if I am approaching this the correct way or not, I've been receiving a myriad of errors trying different methods. If I use the function above it tells me "Must declare @t1", so I modified it so @t1 is declared in the RETURNS statement, and the syntax checks then work, but when I attempt to save the function it tells me "Cannot perform alter on fn_cashaccountids because it is an incompatible object type"
(The code I use to generate this error is:
CREATE FUNCTION fn_cashaccountids ()
RETURNS @t1 TABLE (i INT)
AS
BEGIN
INSERT INTO @t1 VALUES (100)
RETURN
END
Hopefully I've provided enough but not too much info to sift through, it seems to me this would be something encountered a bit before.
Any help is very much appreciated.
- Jeff