Empty Results?

Apr 19, 2007

Okay, so i have a procedure where i'm trying to build a left join, and for some
reason, if i store it into a procedure, it doesnt work, if i run it in query analyzer
i have no problem.

Manual Code:

create PROCEDURE search_orders_11

@SPSirvarchar(3)= null,
@debug bit = 0 AS

DECLARE @sql nvarchar(4000),
@paramlist nvarchar(4000)

if @spsir='1'
select @sql = @sql+ 'SELECT o.state_abbrv, count(o.state_abbrv) as kount FROM dbo.mortgage o left join dbo.Spanish s on o.per1_lname = s.lname'

select @sql=@sql+' WHERE 1 = 1'

SELECT @sql = @sql + ' group by o.state_abbrv ORDER BY o.state_abbrv '



here is the procedure:

create PROCEDURE search_orders_11

@SPSirvarchar(3)= null,
@debug bit = 0 AS

DECLARE @sql nvarchar(4000),
@paramlist nvarchar(4000)

if @spsir='1'
select @sql = @sql+ 'SELECT o.state_abbrv, count(o.state_abbrv) as kount FROM dbo.mortgage o left join dbo.Spanish s on o.per1_lname = s.lname'

select @sql=@sql+' WHERE 1 = 1'

SELECT @sql = @sql + ' group by o.state_abbrv ORDER BY o.state_abbrv '



here is the execution:


exec search_orders_11 @spsir=1



i get the whole command completed sucessfully, but no result. am i missing something here...

View 11 Replies


ADVERTISEMENT

Remove Empty Strings From Results

Feb 6, 2007

i am makin an address block

trying to make it (address1 + ' ' + address2 + ' ' + address3) as address

is there a way to get rid of the address2 if there is nothing in it

View 3 Replies View Related

Transact SQL :: How To Get Zero Or Null Value For Empty Results In Server

Oct 7, 2015

I have written one query like this 

select staffid,staffname,deptname

From staff s join dept d on s.deptid=d.deptid

This query we have no results 

I need this results

staffid staffname deptname
null     null          null

View 7 Replies View Related

Why Search On Empty String Returns Results With A Space?

Aug 1, 2007

In sql server 2000 - our QA pointed out that his testing for empty strings returned 200 + rows but that when he clicked in the field there were obviously a space there. This issue came up because of the script I created to replace and earlier one that queried on empty strings instead of datalength and the earlier script always reported that it had updated x number of rows regardless of how many times it was run on the same database.

QA query based on the earlier script:
Select * from StringTable
WHERE (LongString = '' OR LongString IS NULL)

My script:
The fields are nvarchars in the newer database but older version of the database had varchars. I had created a script to replace empty strings as follows:

-- if LongString column is varchar - run varchar update else nvarchar update
If exists (Select * from sysobjects o
inner join syscolumns c on c.id = o.id
where c.name = 'LongString' and o.name = 'StringTable' and c.xtype = 167) begin

-- update varchar LongString
UPDATE StringTable
SET LongString = char(32)
-- Select * from StringTable
WHERE ((DATALENGTH(LongString ) < 1) OR LongString IS NULL)

END
Else Begin

-- update nvarchar LongString
UPDATE StringTable
SET LongString = char(32)
-- Select * from StringTable
WHERE ((DATALENGTH(LongString ) < 2) OR LongString IS NULL)

END

If exists (Select * from sysobjects o
inner join syscolumns c on c.id = o.id
where c.name = 'ShortString' and o.name = 'StringTable' and c.xtype = 167) begin

UPDATE StringTable
SET ShortString= char(32)
-- Select * from StringTable
WHERE ((DATALENGTH(ShortString) < 1) OR ShortString IS NULL)

END
Else Begin

-- update nvarchar ShortString
UPDATE StringTable
SET ShortString= char(32)
-- Select * from StringTable
WHERE ((DATALENGTH(ShortString) < 2) OR ShortString IS NULL)

END

My method for checking for datalength appears to work correctly why doesn't the QA script? I thought it might have to do with the nvarchar used in the table but I changed the column to a varchar and still has the same issue.

Thanks

View 5 Replies View Related

Copy Table Scripts Results In Empty Return From Sysobjexts?

May 22, 2001

Hi, I wanted to create an exact database in another server, so I generated script of all tables,views, store procedures and ran the script on the new server. I was able to have all objects in the new server.
but when I run the following sql from sql query analyser, I get nothing in return. What do I need in order to get a valid response.

Ali

/*Truncate from all tables*/
select 'Truncate table ' + name from sysobjects where type ='u'
order by name

/*Count all table rows from all tables*/
select 'select count(*) as ' +''+ name + ' from ' + name from sysobjects where type ='u'
order by name

/*View all sp*/

select * from sysobjects where type ='p' and name
not like 'dt%'
order by name

/*View all triggers*/

select * from sysobjects where type ='tr'
order by name

/*View all Views*/
use master
select * from sysobjects where type ='v'
order by name

View 1 Replies View Related

Is There A Way To Hold The Results Of A Select Query Then Operate On The Results And Changes Will Be Reflected On The Actual Data?

Apr 1, 2007

hi,  like, if i need to do delete some items with the id = 10000 then also need to update on the remaining items on the with the same idthen i will need to go through all the records to fetch the items with the same id right?  so, is there something that i can use to hold those records so that i can do the delete and update just on those records  and don't need to query twice? or is there a way to do that in one go ?thanks in advance! 

View 1 Replies View Related

SQL Server 2008 :: Elegant Way For Returning All Results When Subquery Returns No Results?

Mar 25, 2015

I have four tables: Customer (CustomerId INT, CountyId INT), County (CountyId INT), Search(SearchId INT), and SearchCriteria (SearchCriteriaId INT, SearchId INT, CountyId INT, [others not related to this]).

I want to search Customer based off of the Search record, which could have multiple SearchCriteria records. However, if there aren't any SearchCriteria records with CountyId populated for a given Search, I want it to assume to get all Customer records, regardless of CountyId.

Right now, I'm doing it this way.

DECLARE @SearchId INT = 100
SELECT * FROM Customer WHERE
CountyId IN
(
SELECT CASE WHEN EXISTS(SELECT CountyId FROM SearchCriteria WHERE SearchId = @SearchId)
THEN SearchCriteria.CountyId

[Code] .....

This works; it just seems cludgy. Is there a more elegant way to do this?

View 4 Replies View Related

Need To Display Results Of A Query, Then Use A Drop Down List To Filter The Results.

Feb 12, 2008

Hello. I currently have a website that has a table on one webpage. When a record is clicked, the primary key of that record is transfered in the query string to another page and fed into an sql statement. In this case its selecting a project on the first page, and displaying all the scripts for that project on another page. I also have an additional dropdownlist on the second page that i use to filter the scripts by an attribute called 'testdomain'. At present this works to an extent. When i click a project, i am navigated to the scripts page which is empty except for the dropdownlist. i then select a 'testdomain' from the dropdownlist and the page populates with scripts (formview) for the particular test domain. what i would like is for all the scripts to be displayed using the formview in the first instance when the user arrives at the second page. from there, they can then filter the scripts using the dropdownlist.
My current SQL statement is as follows.
SelectCommand="SELECT * FROM [TestScript] WHERE (([ProjectID] = @ProjectID) AND ([TestDomain] = @TestDomain))"
So what is happening is when testdomain = a null value, it does not select any scripts. Is there a way i can achieve the behaivour of the page as i outlined above? Any help would be appreciated.
Thanks,
James.

View 1 Replies View Related

Stored Proc Results Are Displaying In The Messages Tab Instead Of Results Tab- URGENT

May 14, 2008




Hi All,
I have a stored proc which is executing successfully...but the results of that stored proc are displaying in the Messages Tab instaed of results Tab. And in the Results Tab the results shows as 0..So, Any clue friends..it is very urgent..I am trying to call this stored proc in my Report in SSRS as well but the stored proc is not displaying there also...Please help me ASAP..

Thanks
dotnetdev1

View 4 Replies View Related

Mind-boggling Gridview Results! Different Results For Different Teams..

Jun 18, 2008

Hi all, I have the following SQLDataSource statement which connects to my Gridview:<asp:SqlDataSource ID="SqlDataSourceStandings" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"  SelectCommand="SELECT P.firstName, P.lastName, T.teamName, IsNull(P.gamesPlayed, 0) as gamesPlayed, IsNull(P.plateAppearances,0) as plateAppearances, IsNull( (P.plateAppearances - (P.sacrifices + P.walks)) ,0) as atbats, IsNull(P.hits,0) as hits, P.hits/(CONVERT(Decimal(5,2), IsNull(NullIF(P.atbats, 0), 1))) AS [average], (P.hits + P.walks)/(CONVERT(Decimal(5,2), IsNull(NullIF( (P.atbats + P.sacrifices + P.walks) , 0), 1)))  AS [OBP], (P.hits - (P.doubles + P.triples + P.homeRuns) + (2 * P.doubles) + (3 * P.triples) + (4 * P.homeRuns)) / (CONVERT(Decimal(5,2), IsNull(NullIF(P.atbats, 0), 1))) AS [SLG], P.singles, P.doubles, P.triples, P.homeRuns, P.walks, P.sacrifices, P.runs, P.rbis FROM Players P INNER JOIN Teams T ON P.team = T.teamID ORDER BY P.firstName, P.lastName"></asp:SqlDataSource>There are 8 teams in the database, and somehow the average and obp results are as expected for all teams except where T.teamID = 1.  This doesn't make sense to me at all!  For example, I get the following results with this same query: First NameLast NameTeamGPPAABHAVGOBPSLG1B2B3BHRBBSACRRBI

BrianAustinHope83432230.7187500.7352941.15625014612201221

GabrielHelbigSafe Haven62119141.0000000.9375002.1428576404111519

MarkusJavorSafe Haven82927200.8695650.8000001.21739114501021218

RobBennettMelville83029240.8275860.8333331.55172411904102117

AdamBiesenthalSafe Haven82929210.9130430.9130431.56521712631001015

ErikGalvezMelville82625180.7200000.7307691.24000011322101015 As you can see, all teams except for Safe Haven's have the correct AVG and OBP.  Since AVG is simply H/AB, it doesn't make sense for Gabriel Helbig's results to be 1.00000. Can anyone shed ANY light on this please?Thank you in advance,Markuu  ***As a side note, could anyone also let me know how I could format the output so that AVG and OBP are only 3 decimal places? (ex: 0.719 for the 1st result)*** 

View 2 Replies View Related

Removing Individual Results From A Paged Set Of Results.

Oct 19, 2007

Hi,
I have a web form that lets users search for people in my database they wish to contact. The database returns a paged set of results using a CTE, Top X, and Row_number().
I would like to give my users to option of removing individual people from this list but cannot find a way to do this.
I have tried creating a session variable with a comma delimited list of ID's that I pass to my sproc and use in a NOT IN() statement. But I keep getting a "Input string was not in a correct format." Error Message.
Is there any way to do this? I am still new to stored procedures so any advice would be helpful.
Thanks
 

View 3 Replies View Related

PASTE SQL RESULTS INTO EXCEL - Funny Results

Jan 30, 2008

Hi, when I copy and paste results from query analyzer into Excel it appears that values with zeroes at the end loose the zeroes. Example, if I copy and paste V128.0 into an Excel cell it comes out as V128 or if I copy 178.70 it displays as 178.7 - any ideas? I'm using SQL Enterprise Manager for 2000.

View 6 Replies View Related

ALL And Empty Set

Sep 29, 2006

SQL Server 2000 SP4. Hoping to find a logical explanation for a certainbehavior.Consider this script:USE pubsGOIF 1 = ALL (SELECT contract FROM dbo.authors WHERE state = 'CA')PRINT 'TRUE'ELSEPRINT 'FALSE'This, as expected, prints FALSE, since not all authors in CA are undercontract. Now, if the script is changed as follows:USE pubsGOIF 1 = ALL (SELECT contract FROM dbo.authors WHERE state = '')PRINT 'TRUE'ELSEPRINT 'FALSE'then the result is TRUE. In other words, the expression evaluates to TRUEwhen the select statement produces an empty set, which doesn't make senseto me. Even more interesting, the expressionNULL = ALL (SELECT contract FROM dbo.authors WHERE state = '')still evaluates to TRUE (ANSI_NULLS is ON).Can anyone explain these results? Is this the expected behavior in the SQLstandard, or something that is specific to SQL Server? Thanks.--remove a 9 to reply by email

View 5 Replies View Related

Can Anyone Tell Me Why This Returns An Empty Value?

Feb 26, 2007

@Names is a query string passed in, I need to count the number of records as a  result of the below query/ 
 
Dim test As String
Dim sqlConnection3 As New SqlConnection("data Source=EQ-520-WEBSQLEXPRESS;Initial Catalog=CRDB.MDF;Integrated Security=True")
Dim cmd As New SqlCommand
 
Dim returnValue As Object
cmd.CommandText = "SELECT COUNT(ReqID) AS Expr1, LineManager FROM TblReqMain GROUP BY LineManager HAVING (LineManager = @Names)"
cmd.CommandType = Data.CommandType.Text
cmd.Connection = sqlConnection3
cmd.Parameters.Add("@Names", Data.SqlDbType.NVarChar)
sqlConnection3.Open()
cmd.Parameters("@Names").Value = test
If test = "" Then
Response.Write("An error occured")
Exit Sub
Else
returnValue = cmd.ExecuteScalar()
sqlConnection3.Close()
Label6.Text = "Number " & returnValue

View 1 Replies View Related

Datatable Is Empty

Feb 12, 2008

I am fetching records from a table and putting in a datatable using the "sqlda.fill(datatable)"
when i see the datable in "data visualizer" i find no rows.
It is confused eventhough it does not throw any error
 

View 3 Replies View Related

Inserting An Empty Row

Mar 14, 2008

Hi, is there an equivalent to the mysql's " INSERT INTO table () VALUES ();"?    The fields don't allow null values, and there is a primary key with auto increment. Any ideas?Thanks a lot. 

View 7 Replies View Related

Empty The Transaction Log

Jun 28, 2004

Hi i would like to know how to empty the transaction log programmaticly.

Someone got a clue? :)

View 6 Replies View Related

Transaction Log Never Empty

Jan 26, 2000

Hi, I have a SQL Server 6.50 - 6.50.415 (Intel X86) and one database has 8Gb of data and 2Gb of log, the problem is that
the log space used is never below 44%, even if I back it up this way:

DUMP TRANSACTION DBName TO DBNAME_log_930 VOLUME = 'SS0003' WITH NOUNLOAD , STATS = 10, INIT , NOSKIP

This should clean the log, right?

I check for open transactions via: dbcc opentran (GMS), but there aren't.
The way I check for the log space used is: dbcc sqlperf(logspace)
The recover interval is 5.

Any Ideas?

Thank You
Gaspar Gonzalez

View 2 Replies View Related

To Empty Log File

Jun 27, 2005

i have a sql sever2000 , and few disk.
when i am doing many opertaion , the log file is full. so how can i empty it?
sorry for my bad english

View 2 Replies View Related

Bcp Out Empty Column

Feb 27, 2006

I'm bcp-ing a table to a file. I've noticed that if a varchar column is empty, that both "bcp -c" and "bcp -c -k" export the empty value as #$00. Instead, I'd like to export the value as if it were null.


use monkey
go

create table tab1 (
myColumn varchar(10) null
)
go

insert into tab1 (myColumn) values ('')
go

exec master..xp_cmdshell 'bcp monkey..tab1 out D:data.csv -c -T'
go

drop table tab1
go


I've been fiddling about with other switches but so far haven't come up with a solution other than writing out the full SELECT-statement or updating the original. Anyone with an easier solution?

View 3 Replies View Related

Empty A Table

May 14, 2007

hi everyone,

i was just wondering, is there anyway to empty a table...like, in mysql u can go EMPTY TABLE

and it resets the identity stuff, so your id's start at zero again...?

Thanks, Justin

View 4 Replies View Related

Database Empty

Mar 23, 2006

hello everyone,
am not sure if this is the right forum, but here goes.

We have a couple of clients we support running MS SQL, and as a rule, all db access is by SP and select statements.

However, we noticed earlier today, that a newly created account was empty. All columns except the users columns had been deleted.

1. Is there a way of checking which user account deleted the columns ?
2. Or finding out what went wrong ?

This has never happened before

Afrika

View 3 Replies View Related

Empty DB Structure

Apr 24, 2007

Hi,

I have to change collation for certain database. I think the only solution is to export or create through scripts the empty db structure (tables, PK & FK, defaults) and thereafter import data.
Since I don't have empty structure of this database I have to create somehow from the existing one which is with data, can somebody suggest how can I do this - take out the structure of the database from the db with data on it. I have 256 tables and I have to order them first with the dependencies of the tables?!

I will be really thankfull if somebody can help me on this issue.

Thnks,
sqlzagi

View 3 Replies View Related

Sort Empty Last

Nov 14, 2007

Is there any way to sort your results in a way that the empty results comes last? When I sort my recordset on a certain column I first get the empty values and the A, B, C etc. I would like to have it A, B, C, ..., Empty ones.

Regards
Ajmufic

View 6 Replies View Related

Empty Cells

Feb 11, 2008



I am trying to import Data from an Excel file to a SQL DB table. there are around 106 rows and 2 columns.
By default, the ssis is selecting around 200 columns and over 2000 rows. But all cells except for the 2 columns and 106 rows are empty. While I can specify that I need to read only 2 columns, I could not figure out a way to specify the rows. Any help?

View 3 Replies View Related

What To Do With An Empty Result Set

Mar 13, 2008



I'm querying my database with a datetime constraint, only picking up records newer than the last ETL. Although I doubt it would ever happen I was going to put a check in the data flow to trap the case when NO rows were read due to inactivity in the OLAP. I set up a row count and tried to trap it. Apparently if you use an OLE DB source to get a result set and that result set is empty, the program flow bypasses everything and keeps running until it hits something causing an error, like an SQL Insert or Update command.

Is there any way to trap this condition? Currently it is going to cause the program to fail which will cause a snapshot restore (after kicking everyone out of the database).

Thanks.

View 8 Replies View Related

Empty &&<&&> IS NOT NULL

Jul 22, 2007

Hello,



I have a query that returns the appropriate values I need, however there is one field I'd like to add and utilize but my problem is I only want to use it if it contains data.



If I filter it with IS NOT NULL it returns all the records, including the empty records. The field is simply empty, and doesn't come back as NULL. If I filter it with =' ' , it shows all the records with the empty records only.



I need to do the opposite, be able to filter it only if it's not empty.



Any help would be appreciated.

View 4 Replies View Related

What Is The Best Way To Get Empty Date?

Feb 20, 2008

tbl_store
id numeric(9)
enddate datetime(8)

tbl_store Data
ID Enddate
1 01/02/2020
2
3 01/02/2010
4
5 01/02/2001

(empty enddate data is showing <null>)
This is my query...but i want to get date empty records too.
select * from tbl_store where Enddate> '02/20/2008'

I want output
1 01/02/2020
2
3 01/02/2010
4

View 21 Replies View Related

How Do I Empty The Transaction Log

Nov 22, 2005

good day to everybody

View 5 Replies View Related

Empty Resultset From Sql2005

Aug 23, 2006

Hi,
I have used Red Gates SQL bundle to make an identical db on sql2005 express from sql 2000. When i run my query against the sql2000 i get what I want, but when i run it against the sql2005, i get nothing.
Anyone got any suggestions to why?
web.config:
<!-- <add name="TPLConnectionString" connectionString="Data Source=[SQL2000],1433;Network Library=DBMSSOCN;Initial Catalog=TPL;User ID=??????;Password=????????;"
providerName="System.Data.SqlClient" />
-->
<add name="TPLConnectionString" connectionString="Data Source=[SQL2005]\SQLEXPRESS,1433;Integrated Security=true;Network Library=DBMSSOCN;Initial Catalog=TPL;User ID=?????;Password=???????;"
providerName="System.Data.SqlClient" />
default.aspx.cs
dbConnection.Open();
String ISPQuery = "SELECT STATEMENT";
SqlCommand command = new SqlCommand(ISPQuery, dbConnection);
SqlDataReader reader = command.ExecuteReader();
 
Please help!!!
 
Best regards
Terje Kristensen

View 2 Replies View Related

Empty Table Problem

May 16, 2007

I am creating three tables in a DataSet using three stored procedures and I need a way to know that either the tables have at least one row or preferably which if any has no rows.  How can I do that?
using three SP's, one for each table:SqlCommand cmd = new SqlCommand("CompanyCheck", con);SqlCommand cmd2 = new SqlCommand("ContractorVerify", con);SqlCommand cmd3 = new SqlCommand("StoreLocation", con);
  cmd.CommandType = CommandType.StoredProcedure;  cmd2.CommandType = CommandType.StoredProcedure;  cmd3.CommandType = CommandType.StoredProcedure;  cmd.Parameters.AddWithValue("@CompanyID", CompanyID);  cmd2.Parameters.AddWithValue("@CompanyID", CompanyID);  cmd3.Parameters.AddWithValue("@CompanyID", CompanyID);  SqlDataAdapter da = new SqlDataAdapter(); 
da.SelectCommand = cmd; DataSet ds = new DataSet();        try        {            da.Fill(ds, "CompanyInfo");            da.SelectCommand = cmd2;            da.Fill(ds, "ContractorInfo");            da.SelectCommand = cmd3;            da.Fill(ds, "StoreInfo");                   }        catch        { throw new ApplicationException("Data error"); }        finally        {            con.Close();        }
Thank you

View 2 Replies View Related

How To Know If A Record Is Empty Or Not, Without Using QueryString?

May 18, 2007

Hi,I wrote two queries to search in three tables mp_parent, mp_page and mp_parentThe first one is: SELECT mp_page.PID, mp_page.PageID, mp_page.PageContent, mp_page.ParID, mp_page.ChiID,  mp_parent.ParentName FROM mp_page INNER JOIN mp_parent ON mp_page.ParID = mp_parent.ParentID The second one is:SELECT mp_page.PID, mp_page.PageID, mp_page.PageContent, mp_page.ParID, mp_page.ChiID, mp_child.ChildName FROM mp_page INNER JOIN mp_child ON mp_page.ChiID = mp_child.ChildID  I used this way to display the records in a FormView    public HttpContext context = HttpContext.Current;    public void Page_Load(object sender, EventArgs e)    {        if (!Page.IsPostBack)        {            ViewState["srch"] = context.Items["srch"];        }        FormView1.DataSource = GetTable();        FormView1.DataBind();    }      private DataTable GetTable()    {        SqlConnection SqlCon = new SqlConnection("Data Source=AJ-166DCCD87;Initial Catalog=mp;Integrated Security=True;Pooling=False");        String SQL1 = "select mp_page.PID, mp_page.PageID, mp_page.PageContent, mp_page.ParID, mp_page.ChiID, mp_parent.ParentID, mp_parent.ParentName from  mp_page INNER JOIN mp_parent ON mp_page.ParID = mp_parent.ParentID where PageContent like '%" + Convert.ToString(ViewState["srch"]) + "%'";        SqlDataAdapter Adptr = new SqlDataAdapter(SQL1, SqlCon);        SqlCommandBuilder CB = new SqlCommandBuilder(Adptr);        DataTable Dt = new DataTable();        Adptr.Fill(Dt);        return Dt;        SqlCon.Close();    } the question is how can I check if one record is empty to witch to another query ??Is it possible to know without using QueryString?Thank you 

View 5 Replies View Related

If SqlDataSource.Select = Empty Then...?

May 28, 2007

(Newbie). I'm trying to: 1) check if the SELECT command has returned any records, and 2) put a msg box on the screen if there are no records returned from the SELECT query.  The type of code I'm heading towards is:  (but it's wrong). Thank you in advance for your C# code suggestion.
 protected void SqlDataSource1_Selected(object sender, SqlDataSourceStatusEventArgs e)
{if (SqlDataSource1.SelectParameters.Contains is "") then
MessageBox.show ("There are no records available") ;
}

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved