Need to add dup rows to sp

I have a sp that is used for membership cards, there is a column Number_of_Cards. We enter the number of cards a member wants. I need to create duplicate rows in my results based on the number entered in this field for each member. Anyone have any Ideas how to generate dup rows based on this column?

Thanks for any thoughts,Well, I don't like the sounds of it ;-) , but this is how you would do it in your sproc:

DECLARE @.LoopCount int
SELECT @.LoopCount = 1
WHILE @.LoopCount <= @.CardCount
BEGIN
INSERT INTO...
SET @.LoopCount = @.LoopCount + 1
END

Note that Transact-SQL does not have a FOR...NEXT loop construct. You need to make do with a WHILE loop.

Terri|||Thank you, I will give it a try. I know this sounds bad, but it is for a merge to membership cards and I need to do it this way to use Microsoft Word for the process.

Thanks again.|||I have been working with the example and have run into an issue. I thought you might have an idea. I think the problem is that the first loop does the INTO temp_Cards then the next loop fails because the temp_Cards table already exists.

CREATE PROCEDURE dbo.eP_CardProcess2 (@.Start datetime,@.End datetime,@.LoopCount int)
AS SELECT @.LoopCount =1
WHILE @.LoopCount <= (Select dbo.tblPledges.Number_Of_Cards From dbo.tblPledges Where (dbo.tblPledges.StartDate BETWEEN @.Start AND @.End) AND (dbo.tblPledges.Print_Card = 1) AND (dbo.tblPledges.Cards_Printed=0))
BEGIN
SELECT dbo.tblPledges.Number_Of_Cards,dbo.tblPledges.Card_Start_Date,dbo.tblPledges.Card_End_Date,dbo.tblPledges.Alternate_Card_Name,dbo.tblGivers.Email,dbo.tblPledges.RenewalDate INTO #temp_Cards
FROM dbo.tblPledges INNER JOIN dbo.tblClient ON dbo.tblPledges.Client_ID = dbo.tblClient.Client_ID WHERE (dbo.tblPledges.StartDate BETWEEN @.Start AND @.End) AND (dbo.tblPledges.Print_Card = 1) AND (dbo.tblPledges.Cards_Printed=0)
SET @.LoopCount = @.LoopCount + 1
END

sql

Need random/unique "IDs" for key verification

I need to be able to create completely random and unique keys for a key verification system, which would require a user to enter a pre-defined key in order to activate their account, but I need to be able to create those keys on the fly.

This is going to be a key that will be mailed to them on paper, and unfortunately means it needs to be relatively short in order to prevent too much confusion while they are typing it in.

I like the newID() function in SQL, but the ID that it creates is a bit excessive to say the least for someone to have to type when registerring.

I use C#, so I wouldn't have much of a problem creating a small app to create x number of keys, which will sit in the DB until I need them, but I would rather not have to fill the DB with a million or so ID's which might never be used, and don't want to create too little that I have to track when I might need to add more, in case I start to run low on ID's.

Re-using ID's may be an option, but I would prefer to keep them intact for the life of the accounts.

If there is something that I can do to simulate the newID() function, but generate unique/random ID's which look more like this: A97-2C5-77D than this: A972C577-DFB0-064E-1189-0154C99310DAAC12 I would be very grateful to know about it.

Thanks!

You could use Membership.GeneratePassword Method

It allows control of length and complexity both of the generated password

http://msdn2.microsoft.com/en-us/library/system.web.security.membership.generatepassword.aspx

Need line feed in ToolTip text - how?

Hello,

I am displaying a complex formula in a column header tool tip. The formula is generated in a stored procedure. (I do not enter the tool tip text directly in the column header expression.)

Within the stored procedure, how do I generate a string that contains a carriage return/line feed?

Thanks,

BCB

In you sproc insert

select 'here is the break' + char(10) + 'here is the next line' as text

In your tool tip insert

=First(Fields!text.Value)

|||

Thanks for the response, but I don't think your solution will work in my case. The expression that I use for the tooltip text is:

=First(Fields!COL_4.Value, "ToolTips")

You can see that the tooltip is coming from a "ToolTips" dataset. The dataset is produced by a stored procedure. I need to format the tooltip string within the stored procedure that will contain the line feed. I don't believe the Chr(10) would work within a sproc.

Can you think of another approach?

Thanks,

BCB

|||Using CHAR(10) works fine, at least in SQL Server. Your database may be different, but I'd guess it still has some way to embed special characters into string data.|||

You will have to use CHAR(10)+CHAR(13) for line feed and carriage return in your stored proc in SQL Server.

Shyam

|||

I misunderstood Harley Rider's correct response. Thanks to everyone for setting me straight.

BCB