0

I have a single SQL table with columns Mary, Joe, Pat and Mick I have rows of values for each persons weekly expenses

I want a single sproc to query a persons weekly expense value

i.e. a sproc that takes two variables @PersonsName and @WeekNumber and returns a single value

It wont work for me. It will work if I specify the persons name in the query but not if I pass the persons name to the query.

I'm pulling my hair out - is this something to do with Dynamic SQL?

Daire
  • 1
  • 2
  • Entirely doable with a sproc. Post what you have tried so we can help troubleshoot it for you. – Nick Zimmerman May 28 '14 at 20:53
  • 2
    Please change your table design. That is not how you do it. Don't use data as column names. Add some example data and we might help you find a better design. – juergen d May 28 '14 at 20:53

1 Answers1

0

Not sure if you are really digging into performance, scalability etc. as your scenario seems like a small personal tool. But still... as juergen suggested, you better design the schema in a different way. your current design won't scale easily if you want to add one more person to your database. Instead, you can have something like

  • a persons table(person name, person Id)
  • an expenses table (week number, person id, expense)

This scales well. and with indexing on person id your queries can be faster

Suggestions apart, Can I pass variable to select statement as column name in SQL Server seems to provide answer for your question

Community
  • 1
  • 1
krishnaaditya
  • 860
  • 6
  • 5