OK, I have a page that contains a repeater. I figure creating and then calling a stored proc to fill the repeater is the way to go.(please let me know if there is a better way) This is what im looking to do:

My current select statement reads as follows:
SELECT ID, Title, Topic, Summary, Dt_Article, Link FROM CMS_PRData WHERE Active=1 AND Dt_GoLive <= GETDATE() ORDER BY DT_Article DESC

What I need to do is define Link if the value is "" or NULL. If it has data fine, if not I need to set Link to a format similar to this:
"http://www.myserver.com/Article/article.aspx?content=2&ArticleID="ID.value"
ID.Value will equal the value of ID from the row returned.

This is what I have so far but its not working. Im a complete newb to the stored proc world so any help would be appreciated.:

<code>
CREATE proc dbo.DefineLink
(@Link varchar(250) output)
as

declare @TempID int
set nocount on

select @TempID = (SELECT ID from  CMS_PRData WHERE Active=1 AND Dt_GoLive <= GETDATE())-- ORDER BY DT_Article DESC)
select @Link = (SELECT Link from  CMS_PRData WHERE Active=1 AND Dt_GoLive <= GETDATE())-- ORDER BY DT_Article DESC)

If @Link = NULL
 set @Link = 'http://www.myserver.com/Article/article.aspx?content=2&ArticleID=' & @TempID
GO
</code>

TIA,
Stue

This might not be the 'best' solution, but you could use a case/when statement in your select query.

SELECT
 ID
 , Title
 , Topic
 , Summary
 , Dt_Article
 , case when Link IS NULL OR Link = '' THEN
  'http://www.myserver.com/Article/article.aspx?content=2&ArticleID=' + Cast( ID as varchar )
   else
  Link
   END as Link
FROM CMS_PRData
WHERE
 Active=1
 AND Dt_GoLive <= GETDATE()
ORDER BY
 DT_Article DESC

You can use an inline SQL with the powerful CASE  OR COALESCE function here.

Here I am using the COALESCE function to show how we can do. If a record value in  LINK column is NULL in the database, the it pick up the one defined here:

SELECT ID, Title, Topic, Summary, Dt_Article, Link = COALESCE(LINK,'http://www.myserver.com/Article/article.aspx?content=2&ArticleID='+ID) as LINK FROM CMS_PRData WHERE Active=1 AND Dt_GoLive <= GETDATE() ORDER BY DT_Article DESC

If you have issues with this query, please post back.

limno:

You can use an inline SQL with the powerful CASE  OR COALESCE function here.

Here I am using the COALESCE function to show how we can do. If a record value in  LINK column is NULL in the database, the it pick up the one defined here:

SELECT ID, Title, Topic, Summary, Dt_Article, Link = COALESCE(LINK,'http://www.myserver.com/Article/article.aspx?content=2&ArticleID='+ID) as LINK FROM CMS_PRData WHERE Active=1 AND Dt_GoLive <= GETDATE() ORDER BY DT_Article DESC

 

If you have issues with this query, please post back.

Limno, I tried yours first and got "Incorrect syntax near the keyword 'as'."
Billrob Im gunna give yours a shot.

BTW thanks a ton for both your fast replies!

BTW, heres the string (no stored proc for the time being):

Be aware that coalesce will return the first NON-NULL parameter.  In your initial requirements you stated LINK could either be null or "".  If LINK is "", then coalsce will not work as you intended.

HA! right when you posted that billrob I figured out the problem with limnos code:

SELECT ID, Title, Topic, Summary, Dt_Article, Link = COALESCE(LINK, 'http://www.myserver.com/Article/article.aspx?content=2&ArticleID='+ Cast( ID as varchar)) FROM CMS_PRData WHERE Active=1 AND Dt_GoLive <= GETDATE() ORDER BY DT_Article DESC

strQuery = "SELECT ID, Title, Topic, Summary, Dt_Article, case when Link IS NULL OR " _
& "Link = '' THEN 'http://www.anydocsoftware.com/Article/Article.aspx?Category=3&ArticleID=' + " _
& "Cast(ID as char) else Link END as Link FROM CMS_PRData " _
& "ORDER BY DT_Article DESC"
pnlPR.Visible = True
End If

Using sqlDAdapter As Data.SqlClient.SqlDataAdapter = New Data.SqlClient.SqlDataAdapter(strQuery, strConn)
Dim dtTable As Data.DataTable = New Data.DataTable()
sqlDAdapter.Fill(dtTable)
rptA.DataSource = dtTable
rptA.DataBind()
End Using

I figured the repeater was really rptB. :)

Glad it worked out for you.  You should also add COALSCE to your sql bag of tricks.  It comes in VERY handy on occasion. 

Ha! rptB.

Yeah once upon a time a long time ago when I wrote my first stored proc I had a need for COALSCE and used it. Worked for me back then but the need for anythin above basic SELECT statements around here has been pretty low since then unfortunately. So I dont get to hone my SQL skills as much as I should :(

Anyhow, thanks again.. this woulda taken me forever to figure out! great way to cap a Friday. Have a great weekend to both you guys for the help!

Thanks again,
Stue