Query works - sproc fails

I have a query that works fine but fails as a sproc.

QUERY:

SELECT UserName, ProfileId, FirstName, LastName

FROM dbo.CustomProfile JOIN dbo.aspnet_Users

ON dbo.CustomProfile.UserId = dbo.aspnet_Users.UserId

WHERE UserName = 'Brown'

SPROC:

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[GetProfileId]

@.UserName nvarchar

AS

SELECT UserName, ProfileId, FirstName, LastName

FROM dbo.CustomProfile JOIN dbo.aspnet_Users

ON dbo.CustomProfile.UserId = dbo.aspnet_Users.UserId

WHERE UserName = @.UserName

The query returns results. In SQL Server Management Studio when I execute the sproc and enter the value Brown the sproc returns no values; i.e. 0

Set a size to the parameter. That might help.

|||

kerchunk!

That did it. Thanks!

Query with "not null"?

Is there a way to do a query and include a field if it is *not* null?
For instance, I know I can:
Select LastName + isnull(FirstName, '') from tblClients
I want to include a field only if it isn't null, for instance, if a client
is inactive, I want to display "(inactive)" in the results:
Smith, Jane (inactive)
Smith, John
Smith, Joe
Smith, Carol (inactive)
My fields are LastName, FirstName, Inactive (bit)Hi dew
I'm not sure what the connection with NULL is - is Inactive nullable,
so that you want to show (inactive) when Inactive is NULL or 0?
To do this, you can use the CASE statement:
SELECT LastName + isnull(FirstName, '') + CASE WHEN Inactive IS NULL
THEN '(inactive)' ELSE CASE WHEN Inactive=0 THEN ('inactive') ELSE ''
END END
(two nested CASE statements - would only need one if Inactive can only
have values 0 or 1 - i.e. is not NULLable).
hope this helps
Seb|||I'm not sure what you want to do.
But, I can tell you that you results will always contain the same number of
columns for all rows. So, you can't return a different number of columns fo
r
different criteria.
You could definitely build a dynamic string based on your query.
Like
SELECT LastName + ', ' + FirstName + CASE WHEN Inactive =1 THEN '
(inactive)' ELSE '' END FROM YourTable
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"dew" wrote:

> Is there a way to do a query and include a field if it is *not* null?
> For instance, I know I can:
> Select LastName + isnull(FirstName, '') from tblClients
> I want to include a field only if it isn't null, for instance, if a client
> is inactive, I want to display "(inactive)" in the results:
> Smith, Jane (inactive)
> Smith, John
> Smith, Joe
> Smith, Carol (inactive)
> My fields are LastName, FirstName, Inactive (bit)
>
>|||The output of the query must be in table-format; all rows returned must have
the same number of columns. You can get something similar in appearance to
your desired output with something like this
SELECT LastName + ', ' + FirstName AS "Name", "Active"=
CASE
WHEN Inactive = 1 THEN '(inactive)'
ELSE ''
END
FROM [Your Table]
-
"dew" wrote:

> Is there a way to do a query and include a field if it is *not* null?
> For instance, I know I can:
> Select LastName + isnull(FirstName, '') from tblClients
> I want to include a field only if it isn't null, for instance, if a client
> is inactive, I want to display "(inactive)" in the results:
> Smith, Jane (inactive)
> Smith, John
> Smith, Joe
> Smith, Carol (inactive)
> My fields are LastName, FirstName, Inactive (bit)
>
>|||Not sure im understanding you properly but isn't this all you need...
Select LastName + isnull(FirstName, '') from tblClients where Inactive is NU
LL
Select LastName + isnull(FirstName, '') from tblClients where Inactive is
NOT NULL
"dew" wrote:

> Is there a way to do a query and include a field if it is *not* null?
> For instance, I know I can:
> Select LastName + isnull(FirstName, '') from tblClients
> I want to include a field only if it isn't null, for instance, if a client
> is inactive, I want to display "(inactive)" in the results:
> Smith, Jane (inactive)
> Smith, John
> Smith, Joe
> Smith, Carol (inactive)
> My fields are LastName, FirstName, Inactive (bit)
>
>|||Thanks so much, the select with Case works great, that is just what I
needed. Currently the Inactive column can be null but I can change that to
always be 0 or 1 so either one works. Thanks!
"dew" <dew@.yahoo.com> wrote in message
news:%23yUecnhEGHA.2072@.TK2MSFTNGP10.phx.gbl...
> Is there a way to do a query and include a field if it is *not* null?
> For instance, I know I can:
> Select LastName + isnull(FirstName, '') from tblClients
> I want to include a field only if it isn't null, for instance, if a client
> is inactive, I want to display "(inactive)" in the results:
> Smith, Jane (inactive)
> Smith, John
> Smith, Joe
> Smith, Carol (inactive)
> My fields are LastName, FirstName, Inactive (bit)
>

Query to split Firstname Lastname into 2 fields

Good Afternoon Everyone,
I hope everyone is doing GREAT today. I've got a database where my
customers First and Last name are in ONE field (ContactName), and we are
upgrading to another SQL application that actually has (2) seperate fields,
FirstName and LastName. Does anyone know how I can run a query to seperate
the First and Last name and put it into two fields?
Right now this is how the new SQL database is:
FieldNames
FirstName LastName
Anthony Smith
I imported the whole contactname field into the FirstName field. So
Lastname is blank. I'd like to take the last name from the 1st field and
put that into the LastName field.
This is what I'd like to acheive:
FieldNames
FirstName LastName
Anthony Smith
Thanks!
Sincerely,
Anthony Smith
In God We Trust!
Are all the names names formatted the same? If so you can use CHARINDEX or
the LEFT & RIGHT fuctions like:
SELECT LEFT( @.name , CHARINDEX( ' ', @.name ) - 1 )
SELECT RIGHT( @.name , CHARINDEX( ' ', REVERSE( @.name ) ) - 1 )
If they are not formatted the same, you have some issues to ponder. What
should happen if there is a middle name or a middle initial? What if either
the firstname or the last name was missing? How would you address a part of
the name that has more than a single space in it? What about double
barrelled names?
Anith
|||Hi Anthony
The following example should point you in the right direction. A couple of
things to watch out for are people that have two first names ie. Mary Jane
Smith and that the formatting of the data is consistent ie. no double spacing
etc.
CREATE TABLE Names
(
FirstName VARCHAR(20),
LastName VARCHAR(20) NULL
)
INSERT Names SELECT 'Anthony Smith', NULL
INSERT Names SELECT 'Peter Ward', NULL
INSERT Names SELECT 'John Brown', NULL
INSERT Names SELECT 'Prince', NULL
INSERT Names SELECT 'Mary Jane Smith', NULL
UPDATENames
SETFirstName =
CASE
WHEN CHARINDEX(' ', FirstName) > 0 THEN SUBSTRING(FirstName, 1,
CHARINDEX(' ', FirstName) - 1)
ELSE FirstName
END,
LastName =
CASE WHEN CHARINDEX(' ', FirstName) > 0 THEN SUBSTRING(FirstName,
CHARINDEX(' ', FirstName) + 1, LEN(FirstName) - CHARINDEX(FirstName, ' '))
END
SELECT * FROM Names
Returns:
FirstName LastName
-- --
Anthony Smith
Peter Ward
John Brown
Prince NULL
Mary Jane Smith
HTH
- Peter Ward
www.wardyit.com
"Anthony Smith" wrote:

> Good Afternoon Everyone,
> I hope everyone is doing GREAT today. I've got a database where my
> customers First and Last name are in ONE field (ContactName), and we are
> upgrading to another SQL application that actually has (2) seperate fields,
> FirstName and LastName. Does anyone know how I can run a query to seperate
> the First and Last name and put it into two fields?
> Right now this is how the new SQL database is:
> FieldNames
> FirstName LastName
> Anthony Smith
> I imported the whole contactname field into the FirstName field. So
> Lastname is blank. I'd like to take the last name from the 1st field and
> put that into the LastName field.
> This is what I'd like to acheive:
> FieldNames
> FirstName LastName
> Anthony Smith
>
> Thanks!
> Sincerely,
> Anthony Smith
> In God We Trust!
>
>
|||> John Steve St.Smith deWaal III
:-))
create table #t ( [Name] varchar(40))
insert into #t ([Name]) values ('Smith,John E')
insert into #t ([Name]) values ('Smith,Bill')
insert into #t ([Name]) values ('Smith,Adam F')
insert into #t ([Name]) values ('St,Smith deWaal III')
--go
select LastName, FirstName, MiddleName
from (
select
Name,
substring(Name,1,Comma-1) LastName,
substring(Name,Comma+1,Spce-Comma-1) FirstName,
nullif(substring(Name,Spce+1,40),'') MiddleName
from (
select
Name,
charindex(',',Name) Comma,
charindex(' ',Name+space(1),charindex(',',Name)) Spce
from #t
) D
) SplitNames
drop table #t
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u0UwP5aVHHA.192@.TK2MSFTNGP04.phx.gbl...
> ... and be prepared to manually scrub names such as:
> John Steve St.Smith deWaal III
> I haven't used such, but there are tools out these for this particular
> purpose. Depending on how many names you have and the complexity of the
> names, such a tool might be cheaper in the end.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Anthony Smith" <anthony@.peconet.com> wrote in message
> news:%23mepx5UVHHA.3948@.TK2MSFTNGP05.phx.gbl...
>
|||Thank you everyone for the prompts replies. I think most of them are
formatted the same but there may be a few that aren't. If it takes care of
the bulk of the customers that'll be fine, we can manually change the rest.
Have a blessed day everyone!
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:uj1IsLVVHHA.4828@.TK2MSFTNGP05.phx.gbl...
> Are all the names names formatted the same? If so you can use CHARINDEX or
> the LEFT & RIGHT fuctions like:
> SELECT LEFT( @.name , CHARINDEX( ' ', @.name ) - 1 )
> SELECT RIGHT( @.name , CHARINDEX( ' ', REVERSE( @.name ) ) - 1 )
> If they are not formatted the same, you have some issues to ponder. What
> should happen if there is a middle name or a middle initial? What if
> either the firstname or the last name was missing? How would you address a
> part of the name that has more than a single space in it? What about
> double barrelled names?
> --
> Anith
>

Query to extract the most recent information - help please

Hi all,

I have a table, three records of which look like this:

ID PersonID FirstName LastName PostCode
1 999 Barry White BW13 8GS
2 999 <null> <null> BW13 9GS
3 999 <null> Whites <null>

Both these records refer to the same "person". The records with ID of 2 and 3 represent updates to the record with an ID of 1. The problem is, only the updated data (along with the personID) is represented in records 2 and 3. I need to write query that will return a single record that looks like this:

PersonID FirstName LastName PostCode
999 Barry Whites BW13 9GS

in other words, the most recent information we have for that person.

Does anyone have any ideas? I'd be very grateful as this is proving to be a real pain in the butt!

Kind regards,

maccaPersonID FirstName LastName PostCode
999 Barry Whites BW13 9GS

Hi
Try this:

SELECT PersonID, FirstName, LastName, PostCode FROM YourTable
WHERE ID IN(SELECT MAX(ID) FROM YourTable WHERE PersonID = 999)|||Hi shaikh,

Unfortunately, that would just return

ID PersonID FirstName LastName PostCode
3 999 <null> Whites <null>

as it is only selecting the most recent record (or the record with the highest ID).

Thanks for posting though.|||Hi,

You can use the following query, perhaps using CTE may be also solve the problem.

declare @.fn varchar(10), @.ln varchar(10), @.pc varchar(10)
SELECT
@.fn = CASE WHEN firstname is not null THEN firstname ELSE @.fn END,
@.ln = CASE WHEN lastname is not null THEN lastname ELSE @.ln END,
@.pc = CASE WHEN postcode is not null THEN postcode ELSE @.pc END
from persons where personid = 999
select @.fn, @.ln, @.pc

Eralper
http://www.kodyaz.com|||Ohh sorry

Try this. Put this code in stored procedure

SELECT TOP 1
FirstName = (SELECT TOP 1 FirstName FROM Test WHERE FirstName IS NOT NULL ORDER BY [ID] DESC),
LastName = (SELECT TOP 1 LastName FROM Test WHERE LastName IS NOT NULL ORDER BY [ID] DESC),
PostCode = (SELECT TOP 1 PostCode FROM Test WHERE PostCode IS NOT NULL ORDER BY [ID] DESC)
FROM Test WHERE PersonID = 999|||Thanks eralper,

That's a smart solution and in testing it works like a dream. I'd love to understand how it works. Could you elaborate, just a little?

Cheers

Tim|||Hi macca,

The query just updates the values of parameters while reading the selected rows.

This method is also useful while updating data rows in a table.

You can look at the article named "How to use SQL variables in an Update Statements Where Variable is also Updated for each row during the Update Process" at http://www.kodyaz.com/articles/SQL-Variables-In-Update-Statements.aspx

Eralper|||Thanks eralper, that's great. Shaikh, yours worked too so thanks for that.|||declare @.fn varchar(10), @.ln varchar(10), @.pc varchar(10)
SELECT
@.fn = CASE WHEN firstname is not null THEN firstname ELSE @.fn END,
@.ln = CASE WHEN lastname is not null THEN lastname ELSE @.ln END,
@.pc = CASE WHEN postcode is not null THEN postcode ELSE @.pc END
from persons where personid = 999
select @.fn, @.ln, @.pc
That's interesting; I had never seen this construction before.

Is it wise to add an 'order by ID' to make sure the rows are processed in the correct order?|||Hi Ivon,

I've done loads of testing on this construct, rearranged my data and all sorts and it still gives me the right answer! I must say, I'm not entirely sure how but it's great!

macca|||Hi,

I agree that an ORDER BY clause will be better to ensure that the rows processed are in correct order.

I believe that since the default order is same with the insert order of the rows, we get the desired result without an Order By.

Eralper
http://www.kodyaz.com