Qusetion about return values from EXEC('select count(*) from xTable')

Hello everybody!

As the topic:

Can i get the value "count(*)" from EXEC('select count(*) from xTable')

Any helps will be usefull! Thanks!

Hi,

Yes you can do that. it will return a table with one column and a row. Please make sure to specify column name for count(*)

'select count(*) as RowCount from xTable'

|||

How about this ;

create table #results (
cnt int
)

insert #results
exec ('select count(*) from master..sysdatabases')

select cnt from #results

|||

Hi

You can return a parameter from executing a string , but you need to use sp_executesql , like

DECLARE @.iCount INT

EXEC sp_executesql N'SELECT @.iCount= COUNT(*) FROM xTable', 'N'@.iCount INT OUT', @.iCount OUT

SELECT @.iCount

|||

Thanks Shallu, Rod Colledge, and NB2006.

I will try to test whether it works. Thanks !!

Not good at english! Sorry!!

Quotes In BCP

I'm exporting via BCP. I'd like to have quotes around the text values. In
DTS, you have the text qualifier option. Is there a BCP option that
corresponds to the DTS option known as the Text Qualifier?
"SR" <mv2k_2003-news@.yahoo.com> wrote in message
news:7L9mc.5460$1T4.3283@.newssvr27.news.prodigy.co m...
> I'm exporting via BCP. I'd like to have quotes around the text values. In
> DTS, you have the text qualifier option. Is there a BCP option that
> corresponds to the DTS option known as the Text Qualifier?
>
In checking the BCP options, I could not find one that corresponds to DTS...
Steve
|||I cannot find a BCP way to do what you ask. You cannot make a " be the
field terminator. What you can do is still create a DTS package and run it
from the DTSRUN utility if you needed it to be command line.
Jeff Duncan
MCDBA, MCSE+I
"Steve Thompson" <SteveThompson@.nomail.please> wrote in message
news:eSZSddtMEHA.2468@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> "SR" <mv2k_2003-news@.yahoo.com> wrote in message
> news:7L9mc.5460$1T4.3283@.newssvr27.news.prodigy.co m...
In
> In checking the BCP options, I could not find one that corresponds to
DTS...
> Steve
>
|||Yeah I knew about the DTS option. Thanks for the help everybody.
"Jeff Duncan" <jduncan@.gtefcu.org> wrote in message
news:%23hpg0CuMEHA.936@.TK2MSFTNGP11.phx.gbl...
> I cannot find a BCP way to do what you ask. You cannot make a " be the
> field terminator. What you can do is still create a DTS package and run
it
> from the DTSRUN utility if you needed it to be command line.
> --
> Jeff Duncan
> MCDBA, MCSE+I
> "Steve Thompson" <SteveThompson@.nomail.please> wrote in message
> news:eSZSddtMEHA.2468@.TK2MSFTNGP11.phx.gbl...
> In
> DTS...
>
|||SR,

> I'm exporting via BCP. I'd like to have quotes around the text
> values. In DTS, you have the text qualifier option. Is there a
> BCP option that corresponds to the DTS option known as the Text
> Qualifier?
You need to use a format file for this. Using the pubs..authors
table as an example, we'll bcp out of a view that looks like this:
use pubs
go
create view authors_csv as
select null first_quote, * from authors
Note that we are including a dummy column called first_quote
that just returns NULL. It's just a little trick to get the leading
quote on the first column.
The format file looks like this:
8.0
10
1 SQLCHAR 0 0 "\"" 1 first_quote ""
2 SQLCHAR 0 11 "\",\"" 2 au_id ""
3 SQLCHAR 0 40 "\",\"" 3 au_lname ""
4 SQLCHAR 0 20 "\",\"" 4 au_fname ""
5 SQLCHAR 0 12 "\",\"" 5 phone ""
6 SQLCHAR 0 40 "\",\"" 6 address ""
7 SQLCHAR 0 20 "\",\"" 7 city ""
8 SQLCHAR 0 2 "\",\"" 8 state ""
9 SQLCHAR 0 5 "\",\"" 9 zip ""
10 SQLCHAR 0 1 "\"\r\n" 10 contract ""
That dummy column is also in the format file to get the leading
quote on au_id.
Here's the command line:
bcp pubs..authors_csv out authors_csv.dat -fauthors_csv.bcp -S. -T
Linda

Quotation marks

Hi
Can someone tell me what i am doing wrong below:
--
Create table Tb1 (CnID varchar(10),Type varchar(100))
insert into Tb1 values ('7','Joe'+"'"+'s')
select * from Tb1
Drop table Tb1
--
I would like the 2nd column to appear as Joe's in the resultset.
Thank you in advanceTry this:
INSERT INTO Tb1 VALUES('7', 'Joe''s';
HTH
Vern
"MittyKom" wrote:

> Hi
> Can someone tell me what i am doing wrong below:
> --
> Create table Tb1 (CnID varchar(10),Type varchar(100))
> insert into Tb1 values ('7','Joe'+"'"+'s')
> select * from Tb1
> Drop table Tb1
> --
> I would like the 2nd column to appear as Joe's in the resultset.
> Thank you in advance|||Oops, forgot the closing parenthesis:
INSERT INTO Tb1 VALUES('7', 'Joe''s');
"Vern Rabe" wrote:
> Try this:
> INSERT INTO Tb1 VALUES('7', 'Joe''s';
> HTH
> Vern
> "MittyKom" wrote:
>|||escape single quote with a single quote
like
'joe''s'
(P.S: that not a double quote, its 2 single quotes :)|||Hi MittyKom
There is no need for any concatenation of strings. If you use two single
quotes inside outer single quotes, it is interpreted as one single quote in
the string.
So your use of concatenation is unnecessary but your use of the double
quotes (") is incorrect. Most interfaces have a setting called
QUOTED_IDENTIFIER set to on, which means that double quotes are used only to
delimit identifiers, and not user data. So the message you are receiving
refers to the fact that your single quote inside the double quotes is being
interpreted as an identifer, and it makes no sense.
So the cleanest solution is to just make it all one string to insert into
the second column, with the two adjacent single quotes getting interpreted
as one single quote in the string.
Create table Tb1 (CnID varchar(10),Type varchar(100))
insert into Tb1 values ('7','Joe''s')
select * from Tb1
Drop table Tb1
The other solution is to SET QUOTED_IDENTIFIER OFF, and then your original
solution will work (but if you leave it on, other things might break)
HTH
Kalen Delaney, SQL Server MVP
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:DCA8C966-786F-485D-843C-F361804326E8@.microsoft.com...
> Hi
> Can someone tell me what i am doing wrong below:
> --
> Create table Tb1 (CnID varchar(10),Type varchar(100))
> insert into Tb1 values ('7','Joe'+"'"+'s')
> select * from Tb1
> Drop table Tb1
> --
> I would like the 2nd column to appear as Joe's in the resultset.
> Thank you in advance|||Thank you so much Vern and Omnibuzz.
"Omnibuzz" wrote:

> escape single quote with a single quote
> like
> 'joe''s'
> (P.S: that not a double quote, its 2 single quotes :)
>|||I use char(39) I think..
Insert Into Emp (LastName) Values ("O" + char(39) + "clock") -- O'clock
something like that.
I know there are quotes/inside other quotes methods, but those sometimes
come back to haunt me, since I deal with client's databases that I don't
have full control over.
..
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:DCA8C966-786F-485D-843C-F361804326E8@.microsoft.com...
> Hi
> Can someone tell me what i am doing wrong below:
> --
> Create table Tb1 (CnID varchar(10),Type varchar(100))
> insert into Tb1 values ('7','Joe'+"'"+'s')
> select * from Tb1
> Drop table Tb1
> --
> I would like the 2nd column to appear as Joe's in the resultset.
> Thank you in advance

Quick Question... How to use Default Values (after allowing NULL)

Hello,

I have a BIT column which accepts NULL values.

What would be a good method to allow an INSERT (or UPDATE) statement to insert NULL into this column but then automatically change the NULL to 0 (zero). In other words, test for NULLs after INSERT (or UPDATE) and change the value to 0 (zero).

Not exactly sure how to do this with a Trigger. Also, what is that [Formula] option used for (column properties in the Table Design view)... and would this apply with my problem?

Thanks,Look up CREATE TRIGGER in Books Online, and pay special attention to the INSERTED and DELETED virtual table concepts. Then within your trigger:

update YourTable
set YourValue = 0
from YourTable
inner join INSERTED on YourTable.PKEY = INSERTED.PKEY
where YourValue is null

But really, you should be doing your inserts through a stored procedure which uses ISNULL([NewValue], 0)|||Thx for the quick response.

I'll give it a shot.

Quick question to check NULL values in input parameters in a stored procedure

Hi:

I have a stored procedure that calls 3 stored procedures. If some of my input parameters are NULL, I would like to skip the call to another stored procedure. Can you someone please help me with this? I would like to find out what is NULL, before I execute the other stored procedures. Thanks so much.

MA

check with is not null

example

If @.Var1 is not null
begin
exec proc1 @.Var1
end

If @.Var2 is not null
begin
exec proc2 @.Var2
end

If @.Var3 is not null
begin
exec proc3 @.Var3
end

Denis the SQL Menace
http://sqlservercode.blogspot.com/

|||Is there a way loop thru the parameters in one go, because in some instances I am dealing with a set of 50 or more parameters. Thanks.|||

something like this perhaps

declare @.v int
declare @.v2 int
declare @.v3 int

select @.v =1,@.v2 =3

if exists (select * from (select @.v as a union all
select @.v2 union all
select @.v3) z where a is null)
begin
print 'at least one parameter has a null value'
end

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Going back to your initial response, which I think I will respond as an Answer to my question, because it is my best bet at this moment. I do have a quick question in reference to your first response, here it is:

If I have more than parameters, that I need to check for NULL, and if its NULL then dont execute the SP, and vice versa, how would i do that? Can i do something like this, my goal is to check/validate that if all values passed in are NULL, then dont call the sp:

IF @.CitizenshipStatusCode is null and
@.GovtIDTypeCode is null and
@.AlienID is null and
@.EmploymentStatusCode is null and
@.EmployerName is null and
@.EmployerAddress1 is null and
@.EmployerAddress2 is null and
@.EmployerAddress3 is null and
@.EmployerCity is null and
@.EmployerStateCode is null and
@.EmployerZipCode is null and
@.EmployerCountryCode is null and
@.Position is null and
@.WorkForeignPhoneExchange is null and
@.WorkAreaCode is null and
@.WorkPhoneNumber is null and
@.WorkExtension is null and
@.WorkEmail is null and
@.EmploymentYears is null and
@.EmploymentMonths is null and
@.MonthlySalaryAmount is null and
@.MonthlyRentAmount is null and
@.OtherMonthlyIncome is null and
@.ResidenceTypeCode is null and
@.CreatedPersonID is null and
@.UpdatedOn is null and
@.CreatedPersonID is null and
@.UpdatedOn is null
BEGIN
Set @.IsNull = 1
END
ELSE
Set @.IsNull = 0

|||

you could use coalesce since coalesce returns the first non null value

examples

declare @.v varchar(40)
declare @.v2 int
declare @.v3 int

select @.v ='1',@.v2 =3
if coalesce(@.v,@.v2,@.v2,null) is null
begin
select 'is null'
end
else
begin
select 'is NOT null'
end
go

declare @.v varchar(40)
declare @.v2 int
declare @.v3 int

--will be null
if coalesce(@.v,@.v2,@.v2,null) is null
begin
select 'is null'
end
else
begin
select 'is NOT null'
end

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||thanks I think this is what I can use. Also, why do you have the word null at the end, inside the parantheses. Is that necessary? Whats the purpose of that?|||It is not necessary to have NULL at the end. If all of the inputs to COALESCE is NULL then it will return NULL anyway.